PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Using arrays in `WHERE IN` statements

There are the following table structures and data:

CREATE TABLE t_source_category (
  id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  label varchar(255) NOT NULL,
  path varchar(255) NOT NULL,
  pid INT NOT NULL);  

INSERT INTO t_source_category (id, label, path, pid) VALUES (1, 'New York State', '1', 0), (2, 'New York City', '1,2', 1), (3, 'Manhattan', '1,2,3', 2);

I want to run the following statement:

SELECT label FROM t_source_category WHERE id IN (SELECT STRING_TO_ARRAY(path, ',')::INT[] FROM t_source_category WHERE id = 3);

But it reported an error:

ERROR: operator does not exist: integer = integer[]
LINE 1: SELECT label FROM t_source_category WHERE id IN (SELECT STRI...
                                                     ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I found a solution: use UNNEST:

SELECT label FROM t_source_category WHERE id IN (SELECT UNNEST(STRING_TO_ARRAY(path, ',')::INT[]) FROM t_source_category WHERE id = 3);

then output:

label
---
New York State
New York City
Manhattan

What I am confused about is: WHERE IN It should be followed by an array. Why does it say integer = integer[]?

Tags:

2

Answers


  1. 0

    It is a syntax problem. There ate two forms of IN:

    • expression IN (expression, expression, ...)

    • expression IN (SELECT ...)

    The data types of these expressions has to be the same (or implicitly castable). That causes the error message you observe in your first attempt to use the second form.

    The second form can also be written like this in standard SQL:

    • expression = ANY (SELECT ...)

    PostgreSQL additionally offers another comparison:

    • expression = ANY (array expression)

    and that’s the form that you should use if you already have an array.

    Login or Signup to reply.
  2. 0

    IN takes a list of values, not an array. The following query uses =ANY to directly use an array from the subquery to produce the desired results (note, the cast to INT[] is applied to the single row result):

    SELECT label
  FROM t_source_category
  WHERE id =ANY((SELECT STRING_TO_ARRAY(PATH, ',')
                   FROM t_source_category
                   WHERE id = 3)::INT[]);

    There appears to be a defect in PostgreSQL 16.3 (it might exist in other releases, but I haven’t tested them). The semantically equivalent query

    SELECT label
  FROM t_source_category
  WHERE id =ANY((SELECT STRING_TO_ARRAY(PATH, ',')::INT[]
                   FROM t_source_category
                   WHERE id = 3));

    results in the same exception as reported in the OP’s original post:

    ERROR:  operator does not exist: integer = integer[]
LINE 3:   WHERE id =ANY((SELECT STRING_TO_ARRAY(PATH, ',')::INT[]
                   ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts. 

SQL state: 42883
Character: 50
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search