How to SELECT by an array in postgresql?

Question

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.

Mike T · Accepted Answer · 2014-05-01 06:08:22Z

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)

answered May 1, 2014 at 6:08

Mike T

44.4k18 gold badges166 silver badges213 bronze badges

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

2 Comments

jagttt Over a year ago

Thanks. I was thinking it should be much more easier, as this seems a very common use case for array isn't it?

Mike T Over a year ago

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: stackoverflow.com/questions/8760419/…

cha · Accepted Answer · 2014-05-01 05:51:31Z

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];

edited May 1, 2014 at 5:51

answered May 1, 2014 at 5:42

cha

10.4k1 gold badge20 silver badges27 bronze badges

1 Comment

Eelke Over a year ago

You can also use the any operator SELECT id, name FROM tmp WHERE id = ANY ARRAY[3, 1, 2];

Ilesh Patel · Accepted Answer · 2014-05-01 05:53:05Z

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

answered May 1, 2014 at 5:53

Ilesh Patel

2,15516 silver badges27 bronze badges

Collectives™ on Stack Overflow

How to SELECT by an array in postgresql?

3 Answers 3

2 Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Linked

Related