WHERE name = 'cat' does not work when `WHERE name LIKE '% cat` works on PostGres
I have a very simple query in Postgres that fails with WHERE =
but works with WHERE LIKE
. I have tags, some of them have a name containing the letters "cat":
pgdb=> SELECT * FROM tagging_tag WHERE tagging_tag.name LIKE '%cat%'
ORDER BY tagging_tag.name ASC;
id | name | synonym_id
------+---------------------+------------
68496 | blackcat |
4 | cat |
28461 | catfight |
I seem to have one tag called "cat", but if I try to get it on my own it doesn't work:
pgdb=> SELECT * FROM tagging_tag WHERE tagging_tag.name = 'cat'
ORDER BY tagging_tag.name ASC;
id | name | synonym_id
---+------+------------
(0 line)
But if I try with a partial LIKE
, it works:
pgdb=> SELECT * FROM tagging_tag WHERE tagging_tag.name LIKE '%cat'
ORDER BY tagging_tag.name ASC;
id | name | synonym_id
------+---------------------+------------
68496 | blackcat |
4 | cat |
pgdb=> SELECT * FROM tagging_tag WHERE tagging_tag.name LIKE 'cat%'
ORDER BY tagging_tag.name ASC;
id | name | synonym_id
------+----------------+------------
4 | cat |
28461 | catfight |
I tried to check the size by thinking of an invisible char, but no chance:
pgdb=> SELECT char_length(name), * FROM tagging_tag WHERE tagging_tag.name LIKE 'cat%'
ORDER BY "tagging_tag"."name" ASC;
char_length | id | name | synonym_id
------------+-------+----------------+------------
3 | 4 | cat |
8 | 28461 | catfight |
I have done some tests and it seems that some tags can be extracted with =
, some of them cannot and I cannot find common denominators between them: the number of letters varies, they are all lowercase ASCII letters, the identifier is not grouped, etc. etc.
Here is the EXPLAIN:
EXPLAIN SELECT * FROM tagging_tag WHERE tagging_tag.name = 'cat'
ORDER BY tagging_tag.name ASC;
QUERY PLAN
-------------------------------------------------------------------------------------
Index Scan using tagging_tag_name on tagging_tag (cost=0.29..4.31 rows=1 width=19)
Index Cond: ((name)::text = 'cat'::text)
(2 lignes)
Some table context:
pgdb=> \d tagging_tag
Table « public.tagging_tag »
Colonne | Type | Modificateurs
-----------+-----------------------+-------------------------------------
id | integer | non NULL Par défaut, nextval('...
name | character varying(50) | non NULL
synonym_id | integer |
Index :
"tagging_tag_id_pkey" PRIMARY KEY, btree (id)
"tagging_tag_name" UNIQUE, btree (name)
"tagging_tag_synonym_id" btree (synonym_id)
Foreign key contraints :
"tagging_tag_synonym_id_fkey" FOREIGN KEY (synonym_id) REFERENCES tagging_tag(id)
Referenced by :
TABLE "tagging_tag" CONSTRAINT "tagging_tag_synonym_id_fkey"
FOREIGN KEY (synonym_id) REFERENCES tagging_tag(id)
Postgres version is 9.3.6.
source to share
Since both tests with name LIKE '%cat'
and name LIKE 'cat%'
returned the same string, and it includes the string 'cat' only once (or does it?), It logically follows that it name = 'cat'
should return the same string as well.
Leading or trailing whitespace cannot explain it.
Other explanations include:
-
Misunderstanding and you tested different databases / different tables, different
search_path
or something along those lines. -
Misunderstanding of another type: there is a line break in your line, the action is as follows:
cat cat
and did you miss the second line?
-
Corrupt index. The output
EXPLAIN
shows which index can be used. Recreate the involved indexes and see if this fixes the problem. Your update to the question shows that it should be an indextagging_tag_name
:REINDEX INDEX tagging_tag_name;
An alternative to brute force would be:
VACUUM FULL tagging_tag;
Rewrites the entire table and all indexes on it (using an exclusive lock).
source to share