Postgres keep order of array

October 16, 2023

I’ve got a simple array of IDs and need the query to reply with the same order

the query

SELECT id, title FROM pages WHERE id = ANY(ARRAY['6e3e4470', 'fec27534'])

the result

id          title
fec27534    2020
6e3e4470    2022

as you can see the array provides the "2022" ID first, but the response replies with it the last result
is there a way to keep this ordered by the array provided?

Thanks in advance

>Solution :

You can use unnest(...) WITH ORDINALITY and an INNER JOIN to retain the array index:

SELECT pages.id, pages.title
FROM pages
INNER JOIN unnest(ARRAY['6e3e4470', 'fec27534'])
               WITH ORDINALITY tbl(id, idx) ON pages.id=tbl.id
ORDER BY tbl.idx