Getting records corresponding to an array of primary keys in the same order as in the array

Question

I have a list / array of primary keys, now I need to execute an SQL query to get the records from the table in the same order as in the array. For instance:

| id | text | | 1 | random data 1 | | 2 | random data 2 | | 3 | random data 3 | | 4 | random data 4 | 

This request:

 select * from sample where id in (2,4,1) 

Should return lines:

 | 2 | random data 2 | | 4 | random data 4 | | 1 | random data 1 | 

What is the best way to do this in PostgreSQL 9.3?

Answer 1

One of the methods:

 SELECT t.id, t.text FROM ( SELECT *, row_number() OVER () AS rn FROM ( SELECT unnest('{2,4,1}'::int[]) AS id ) x ) y JOIN tbl t USING (id) ORDER BY rn 

The trick is to apply the row number using the row_number() window function , which you can use for ORDER BY after JOIN .
Find other ways (more secure among others), a detailed explanation and links in this closely related question on dba.SE.