"ERROR: operator does not exist : integer = integer[]" when using "IN" operator in PostgreSQL. Why do I get this error?

Question

What I'm trying to achieve

First I need to query table tableY to get all userids that fulfill the inner WHERE condition. Then I aggregate it into an array of userids with array_agg(userid). Then in the outer query, I need to select users from the tableX table with userids that exist inside the array I created before from tableY.

Error

I get the following error:

ERROR: operator does not exist: integer = integer[]

LINE 2: WHERE 3 IN ((

HINT: No operator matches the given name and argument types. You might need to add explicit type cast.

My query:

SELECT * FROM mydb.tableX 
WHERE 3 IN ((
SELECT array_agg(userid) userids FROM
  (
    SELECT 
    DISTINCT(uc.userid), eui.firstname
    FROM mydb.tableY uc 
    JOIN mydb.tableX eui ON uc.userid = eui.auth_userid
    WHERE uc.level = 4 
    AND uc.subjectid = 1
    AND uc.lineid = 5 
    GROUP BY uc.userid, eui.firstname
    ORDER BY eui.firstname
  ) AS userids 
))

Btw, I only use the "3" as a hard coded example now to get the query running in the first place.

Question

Why do I get the error?

Thanks!

While IN can also be used with arrays, IN (SELECT …) checks whether any of the results of the subquery match the operand. Just omit the array_agg(…) call and it should work. — Bergi
– Bergi, Commented Apr 21, 2021 at 2:32
Thank you @Bergi . This helped. I just removed the array aggregate and followed the answer below. — EggBender
– EggBender, Commented Apr 21, 2021 at 3:46
You forgot to add the query and the error message into the body — user330315
– user330315, Commented Jun 14, 2021 at 12:18

Pavel Stehule · Accepted Answer · 2021-04-21 03:41:23Z

1

The array_agg is useless in this context. It is only significant overhead and in block some possible optimization.

Write just WHERE d IN (SELECT userid ...

Note - when you really need to check if some value is in an array, you should to use operator = ANY(), but this is not this case:

postgres=# SELECT 1 WHERE 1 = ANY(ARRAY[1,2,3]);
┌──────────┐
│ ?column? │
╞══════════╡
│        1 │
└──────────┘
(1 row)

answered Apr 21, 2021 at 3:41

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

"ERROR: operator does not exist : integer = integer[]" when using "IN" operator in PostgreSQL. Why do I get this error?

What I'm trying to achieve

Error

Question

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

What I'm trying to achieve

Error

Question

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related