0

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.

jagttt
  • 1,020
  • 1
  • 12
  • 25

3 Answers3

1

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)
Mike T
  • 41,085
  • 18
  • 152
  • 203
  • Thanks. I was thinking it should be much more easier, as this seems a very common use case for array isn't it? – jagttt May 02 '14 at 02:33
  • `generate_subscripts` can also be used to get the array subscripts, but it wouldn't make it much simpler. There's a good related answer here: http://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number – Mike T May 02 '14 at 03:25
0

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];
cha
  • 10,301
  • 1
  • 18
  • 26
  • You can also use the any operator `SELECT id, name FROM tmp WHERE id = ANY ARRAY[3, 1, 2];` – Eelke May 01 '14 at 05:48
0

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

Ilesh Patel
  • 2,053
  • 16
  • 27