Postgresql Select rows where column = array
SELECT *
FROM table
WHERE some_id = ANY(ARRAY[1, 2])
or ANSI
-compatible:
SELECT *
FROM table
WHERE some_id IN (1, 2)
The ANY
syntax is preferred because the array as a whole can be passed in a bound variable:
SELECT *
FROM table
WHERE some_id = ANY(?::INT[])
You would need to pass a string representation of the array: {1,2}
For dynamic SQL use:
'IN(' ||array_to_string(some_array, ',')||')'
Example
DO LANGUAGE PLPGSQL $$
DECLARE
some_array bigint[];
sql_statement text;
BEGIN
SELECT array[1, 2] INTO some_array;
RAISE NOTICE '%', some_array;
sql_statement := 'SELECT * FROM my_table WHERE my_column IN(' ||array_to_string(some_array, ',')||')';
RAISE NOTICE '%', sql_statement;
END;
$$;
Result:
NOTICE: {1,2}
NOTICE: SELECT * FROM my_table WHERE my_column IN(1,2)