I understand that Postgresql may return results in non-deterministic order when the query parameters are non-unique. This is especially a problem when we use LIMIT since this may return entirely different results. So the suggestion for safety is to use ORDER BY with LIMIT.

However, what if we use ORDER BY on an non-unique column name? Will this produce a deterministic result or no? Let me demonstrate with an example:

We have the following data:

id | name | index
1    foo     5
1    bar     2
1    test    5

If I query SELECT * FROM <table> WHERE id = 1 ORDER BY index LIMIT 1;. Will this produce the same result every time?

  • Only a deterministic ORDER BY will produce a deterministic result. ORDER BY index is not deterministic. – Erwin Brandstetter Apr 21 '19 at 23:10

If there are multiple rows with the lowest value for index, then no, there is no guarantee you will always get the same row. If you only specify ordering for a column that contains duplicates, then the database does not have to provide anything more than any row that meets the criteria.

Same would be true if you were to say ORDER BY index DESC - which row with 5 would you expect? Why?

In order to force determinism / predictability, add a tie-breaker on a column that is unique (or at least unique within the same values of index). With your sample data, you could say:

ORDER BY index, name LIMIT 1;

But you haven’t shared enough about your table structure and constraints to get a more specific suggestion than that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.