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-24 13:35:18Z

288

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, 2012 at 13:35

answered May 24, 2012 at 13:28

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

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

7 Comments

Jimmy Pitts Over a year ago

Thanks, that works! What I ended up doing was: ".. some_id = ANY(ARRAY[" . implode(",", $id_array) . "])"

Angry 84 Over a year ago

Hello SQL inject with the above comment... (implode php into a sql query)

WhiteWolfza Over a year ago

the following should work better: SELECT * FROM table WHERE some_id = ANY(unnest(?::INT[]))

Quassnoi Over a year ago

@WhiteWolfza: why do you think it would work better?

yiwen Over a year ago

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?

|

Ufos · Accepted Answer · 2017-10-23 14:57:34Z

8

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, 2017 at 14:57

answered Mar 23, 2017 at 9:09

Ufos

3,3162 gold badges35 silver badges39 bronze badges

3 Comments

FlavorScape Over a year ago

i don't think = IN is proper syntax

Ufos Over a year ago

@FlavorScape, oh my -- fixed! Althogh, it did run perfectly (ofc I test an example I post...), but that definetely looks better without the "="

FlavorScape Over a year ago

definitely fails with PostgreSQL driver

jking · Accepted Answer · 2020-09-21 13:45:44Z

5

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.

answered Sep 21, 2020 at 13:45

jking

3123 silver badges10 bronze badges

Comments

Alejandro Salamanca Mazuelo · Accepted Answer · 2014-04-08 15:50:29Z

3

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

answered Apr 8, 2014 at 15:50

Alejandro Salamanca Mazuelo

1,41119 silver badges24 bronze badges

1 Comment

peschanko Over a year ago

You also need to check input data: $nums = array(); foreach ($array as $a) if (is_numeric($a)) $nums [] = $a;

Collectives™ on Stack Overflow

Postgresql Select rows where column = array

4 Answers 4

7 Comments

Example

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Example

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related