Maintaining Input Sort Order in Postgres

Nov 19, 2020 :: 2 min read :: sql postgres

If there’s one thing people love more than data, it’s sorted data. Take for example a list of blog articles. Perhaps you have a search that uses ElasticSearch for query aggregations and it spits out an ordered list of ids that you then look up in Postgres. That query might look something like this:

SELECT * FROM posts
WHERE name IN ($1);

The issue with this query is the order of the result set is not guaranteed to match the order of the IN statement. All the sorting ElasticSearch did gets chucked out the window.

One option is to handle this in application code. We can preserve order by matching the rows to the original list of ids. It would be an additional loop, but should be pretty simple to implement in most languages.

However, there is a better way. We can handle this entirely within Postgres with a little help from UNNEST and WITH ORDINALITY.

Here is the query in its entirety:

SELECT * FROM posts
JOIN (select * from unnest($1::int[]) WITH ORDINALITY AS sorted_posts(post_id, provided_order)
ON sorted_posts.post_id = posts.id
ORDER BY sorted_posts.provided_order
WHERE name in ($1);

Let’s break it down what this query is doing

SELECT * FROM UNNEST($1::Int[])

UNNEST is a Postgres function that expands an array into a set of rows.

SELECT * FROM UNNEST(ARRAY[1,2,3]);
 unnest
--------
      1
      2
      3
(3 rows)
WITH ORDINALITY

WITH ORDINALITY will append a bigint column to the end of the functions output that starts with 1 and is incremented for each row of output. Combined, UNNEST and WITH ORDINALITY provide an in memory representation of the provided IDs and their original ordering.

ON sorted_posts.post_id = posts.id
ORDER BY sorted_posts.provided_order
WHERE name in ($1);

With our final posts and an ordered list of IDs, now we can order the posts by joining to the table created by UNNEST WITH ORDINALITY. The result is our post data with the correct order. All with just one query.

Questions or comments? Feel free to contact me.