Postgresql Select rows where column = array

Question

This is a summary of what I am trying to do:

$array[0] = 1;
$array[1] = 2;

$sql = "SELECT * FROM table WHERE some_id = $array"

Obviously, there are some syntax issues, but this is what I want to do, and I haven't found anything yet that shows how to do it.

Currently, my plan is to do something along these lines:

foreach($idList as $is)
    $where .= 'some_id=' . $id . ' OR';
endforeach

$sql = "SELECT * FROM table WHERE " . $where;

So is there support in PostgreSQL to use an array to search, or do I have to do something similar to my solution?

Quassnoi · Accepted Answer · 2012-05-24T13:35:18.193

264

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}

edited May 24 '12 at 13:35

answered May 24 '12 at 13:28

Quassnoi

413,100
91
616
614

1

Thanks, that works! What I ended up doing was: ".. some_id = ANY(ARRAY[" . implode(",", $id_array) . "])" – Jimmy Pitts May 24 '12 at 14:12
2

Hello SQL inject with the above comment... (implode php into a sql query) – Angry 84 Aug 23 '18 at 04:00
the following should work better: SELECT * FROM table WHERE some_id = ANY(unnest(?::INT[])) – WhiteWolfza Feb 06 '20 at 12:41
1

@WhiteWolfza: why do you think it would work better? – Quassnoi Feb 06 '20 at 21:20
1

This doesn't seem to preserve the returning order defined by array input. i.e. `select id from users where id in (2, 1)` and `select id from users where id in (1, 2)` always return in the same order: `1, 2`. How do I return `2, 1` in the first query? – yiwen Jun 02 '20 at 17:52
@yiwen: it's a good question which is outside the scope of this one. Please post it as a separate question and I'll give you the answer there. – Quassnoi Jun 02 '20 at 19:29
@Quassnoi good call, thanks. The question link: https://stackoverflow.com/q/62182218/198616 – yiwen Jun 03 '20 at 20:31

Ufos · Answer 2 · 2017-10-23T14:57:34.647

7

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)

edited Oct 23 '17 at 14:57

answered Mar 23 '17 at 09:09

Ufos

3,083
2
32
36

i don't think = IN is proper syntax – FlavorScape Oct 17 '17 at 16:47
@FlavorScape, oh my -- fixed! Althogh, it did run perfectly (ofc I test an example I post...), but that definetely looks better without the "=" – Ufos Oct 23 '17 at 14:58
1

definitely fails with PostgreSQL driver – FlavorScape Nov 09 '17 at 17:12

score 5 · Answer 3 · answered Sep 21 '20 at 13:45

In my case, I needed to work with a column that has the data, so using IN() didn't work. Thanks to @Quassnoi for his examples. Here is my solution:

SELECT column(s) FROM table WHERE expr|column = ANY(STRING_TO_ARRAY(column,',')::INT[])

I spent almost 6 hours before I stumble on the post.

score 3 · Answer 4 · answered Apr 08 '14 at 15:50

3

   $array[0] = 1;
   $array[2] = 2;
   $arrayTxt = implode( ',', $array);
   $sql = "SELECT * FROM table WHERE some_id in ($arrayTxt)"

answered Apr 08 '14 at 15:50

Alejandro Salamanca Mazuelo

1,173
15
21

1

You also need to check input data: $nums = array(); foreach ($array as $a) if (is_numeric($a)) $nums [] = $a; – peschanko Feb 05 '19 at 07:57

Postgresql Select rows where column = array

4 Answers4

Example

Linked