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

Question

I'm trying to create a small function that will return all the clients in an array but I keep bumping into errors.

DO $$
DECLARE
    c_ids INTEGER[] := array[12879,16759];
BEGIN
  SELECT *
  FROM   client
  WHERE  id IN (c_ids);
END $$;

What would be the right way to do something like this? I need the c_ids to be a variable because I will be using it in many places in the query (the sample straight forward query is not the end query).

Not a super pro at postgres but I believe you need to use curly braces for the data. This post is an example with a loop but I think it might help you stackoverflow.com/questions/9783422/postgres-array-for-loop — Jacob H
– Jacob H, Commented Feb 23, 2018 at 15:31
@JacobH The curly braces are for writing an array as a string e.g. '{1,2,3}' vs ARRAY[1,2,3]. End result is the same. — 404
– 404, Commented Feb 23, 2018 at 16:31

user330315 · Accepted Answer · 2018-02-23 15:32:52Z

5

The IN operator compares values using the = operator. If you want to check values in an array, you need to use ANY:

DO $$
DECLARE
    c_ids INTEGER[] := array[12879,16759];
BEGIN
  SELECT *
  FROM   client
  WHERE  id = ANY (c_ids);
END $$;

answered Feb 23, 2018 at 15:32

user330315

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related