Question 266340 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres keep order of array

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    Since you would not know whether all ids will be found, the ordering is probably not super useful, you should probably check it on the client against the input anyway.

    You can however do it in Postgres as well:

    SELECT ids.id, pages.title
FROM UNNEST(ARRAY['6e3e4470', 'fec27534']) WITH ORDINALITY AS ids(id)
LEFT JOIN pages USING (id)
ORDER BY ordinality;

    (online demo)

    If you do want to get back only the found rows, use an inner JOIN instead of the LEFT JOIN.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search