Pass an array to a query in Postgres to check if a value is contained in the array

Question

I have an array with some ids on the front-end and I'm writing a query to check if I can find any user with a user_id inside the array, the table is very simple:

CREATE TABLE user_accounts (
  user_id UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  name TEXT,
  connector_type connector_type_enum NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

The array on the front-end is something like ["328hdgyas78g2", "3adhdayas7453"]

and here's the query:

    SELECT  *
    FROM    user_accounts
    WHERE   user_id IN $1

of course there's a syntax error, I have tried the following:

    SELECT  *
    FROM    user_accounts
    WHERE   user_id = ANY(ARRAY["328hdgyas78g2", "3adhdayas7453"])

but I get the following error:

ERROR:  column "328hdgyas78g2" does not exist

How can I fix the query?

Ftisiot · Accepted Answer · 2022-07-26 15:33:04Z

1

That's because the " is used to reference a column or table name. To reference a string you use the single '

You have a couple of options there

WHERE   user_id = ANY(ARRAY['328hdgyas78g2', '3adhdayas7453'])

or

WHERE   user_id = ANY('{328hdgyas78g2,3adhdayas7453}')

Also, good reference is this

answered Jul 26, 2022 at 15:33

Ftisiot

1,8981 gold badge9 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Pass an array to a query in Postgres to check if a value is contained in the array

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