CODEWITHSUNDEEP
sqlpostgresql
jagttt
jagttt

Reputation: 1070

How to SELECT by an array in postgresql?

Say I have an table like this:

DROP TABLE tmp;
CREATE TABLE tmp (id SERIAL, name TEXT);
INSERT INTO tmp VALUES (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five');  
SELECT id, name FROM tmp;

It's like this:

 id | name  
----+-------
  1 | one
  2 | two
  3 | three
  4 | four
  5 | five
(5 rows)

Then I have an array of ARRAY[3,1,2]. I want to get query the table by this array, so I can get an array of ARRAY['three', 'one', 'two']. I think this should be very easy but I just can't get it figured out.

Thanks in advance.

Upvotes: 0

Views: 3844

Answers (3)

Mike T
Mike T

Reputation: 43642

To preserve the array order, it needs to be unnested with the index order (using row_number()), then joined to the tmp table:

SELECT array_agg(name ORDER BY f.ord)
FROM (
  select row_number() over() as ord, a
  FROM unnest(ARRAY[3, 1, 2]) AS a
) AS f
JOIN tmp ON tmp.id = f.a;

    array_agg
-----------------
 {three,one,two}
(1 row)

Upvotes: 1

Ilesh Patel
Ilesh Patel

Reputation: 2155

If you want to return the array as output then try this:

SELECT array_agg(name) FROM tmp WHERE id = ANY (ARRAY[3, 1, 2]);

SQL FIDDLE

Upvotes: 0

cha
cha

Reputation: 10411

Use unnest function:

SELECT id, name FROM tmp
WHERE id IN (SELECT unnest(your_array));

There is a different technique as suggested by Eelke: You can also use the any operator 

SELECT id, name FROM tmp WHERE id = ANY ARRAY[3, 1, 2];

Upvotes: 0

Related Questions