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?

score 1 · Accepted Answer · answered Jul 26 '22 at 15:33

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

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

1 Answers1