CODEWITHSUNDEEP
c#postgresqldappernpgsql
Oliver Weichhold
Oliver Weichhold

Reputation: 10306

PostgreSQL missing operator when using IN predicate with string array

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

Upvotes: 3

Views: 557

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324841

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.

Upvotes: 5

Related Questions