Missing PostgreSQL operator when using IN predicate with array of strings
Given this table structure:
CREATE TABLE tags
(
id SERIAL NOT NULL PRIMARY KEY,
tagname TEXT NOT NULL UNIQUE,
authorid int NOT NULL,
created timestamp NOT NULL,
lastmodified timestamp NOT NULL,
constraint fk_authorid_tags foreign key(authorid) references users(id)
);
Why does the following request fail:
ERROR: operator does not exist: text = text[]
LINE 2: select * from tags where tagname in ('{"c#","c"}'::text[])
Query:
select * from tags where tagname in ('{"c#","c"}'::text[])
source to share
IN
must contain a literal list like
tagname IN ('c#', 'c')
If you want an array, you should use = ANY
:
tagname = ANY (ARRAY['c#', 'c'])
The error occurs because it is tagname IN (somearray)
interpreted as a request " tagname
equal to any element of a 1-element list (somearray)
". This means testing tagname
for equality with a somearray
single element. As for the comparison text
and text[]
is not an operator =
, it is not met.
In contrast, it = ANY
says: "For any element of the array on the right side, is it the left operand equal to the element?". This is how it works.
source to share