Order of returned rows with IN statement
WITH ORDINALITY
in Postgres 9.4+
Introduced with Postgres 9.4. The manual:
When a function in the
FROM
clause is suffixed byWITH ORDINALITY,
abigint
column is appended to the output which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such asunnest()
.
SELECT u.*
FROM unnest('{13589, 16674, 13588}'::int[]) WITH ORDINALITY AS x(id, order_nr)
JOIN users u USING (id)
ORDER BY x.order_nr;
array or set?
x IN (set)
statements are rewritten internally in Postgres to x = ANY (array)
, which is equivalent:
SELECT users.id FROM users WHERE users.id = ANY ('{13589, 16674, 13588}')
You can see for yourself with EXPLAIN
.
Postgres 9.3 or earlier
For now, to preserve the order of elements, you could:
SELECT u.*
FROM (
SELECT arr, generate_subscripts(arr, 1) AS order_nr
FROM (SELECT '{13589, 16674, 13588}'::int[]) t(arr)
) x
JOIN users u ON u.id = x.arr[x.order_nr]
ORDER BY x.order_nr;
db<>fiddle here
Further reading:
- How to preserve the original order of elements in an unnested array?
If you want to rewrite the condition slightly you could do something like this:
with id_list (id, sort_order) as (
values
(13589, 1),
(16674, 2),
(13588, 3)
)
select u.id
from users u
join id_list il on il.id = u.id
order by il.sort_order;
You can also put the values
clause directly into the join:
select u.id
from users u
join (
values
(13589, 1),
(16674, 2),
(13588, 3)
) as il(id, sort_order) on il.id = u.id
order by il.sort_order;