PostgreSQL missing operator when using IN predicate with string array

Question

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 query fails with the error:

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[])

Craig Ringer · Accepted Answer · 2014-08-20 08:34:54Z

5

IN must contain a literal list, e.g.

tagname IN ('c#', 'c')

If you want an array, you must use = ANY:

tagname = ANY (ARRAY['c#', 'c'])

The error is arising because tagname IN (somearray) is interpreted as the query "is tagname equal to any element of the the 1-element list (somearray)". That means testing tagname for equality against somearray, the only element. As there's no = operator to compare text and text[], this fails.

By contrast, = ANY says "For any element of the array on the right hand side, is the left hand operand equal to the element?". So it works.

answered Aug 20, 2014 at 8:34

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oliver Weichhold Over a year ago

Thanks for the thorough explanation Craig.

Collectives™ on Stack Overflow

PostgreSQL missing operator when using IN predicate with string array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related