ANSI SQL proposal: SELECT DISTINCT ON (... ORDER BY ...) and UNION DISTINCT ON (... ORDER BY ...)
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253274psql -h localhost -U postgresBuilt from patchset v3 (message #3), September 16, 2026 at 08:53 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253274_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253274_3 && git checkout t253274_3Patchset v3 (message #3) is on t253274_3
Hi Vik
Finally had time to put this SQL Standard Propoasl together.
Please take a quick look and tell me what is missing or wrong and what
the next steps should be.
For example, should I mention somewhere that PostgreSQL supports a
partial version of the SELECT DISTINCT ON () ?
Regarding PGQ in PostgreSQL, this should allow a plain SQL target for
arbitrary length graph queries and at least some types of path finding
queries.
I hope to have some working PoC code for PostgreSQL within the next
few days so I can share actual working versions of both sample
recursive graph queries and the "overlay of read-only table" queries I
talked to you about.
-----
Hannu
Attachments:
xxx000-distinct-on-clause.mdtext/markdown; charset=US-ASCII; name=xxx000-distinct-on-clause.mdDownload
Here is a patch set implementing this
On Sat, Aug 1, 2026 at 3:08 PM Hannu Krosing <hannuk@google.com> wrote:
Show quoted text
Hi Vik
Finally had time to put this SQL Standard Propoasl together.
Please take a quick look and tell me what is missing or wrong and what
the next steps should be.For example, should I mention somewhere that PostgreSQL supports a
partial version of the SELECT DISTINCT ON () ?Regarding PGQ in PostgreSQL, this should allow a plain SQL target for
arbitrary length graph queries and at least some types of path finding
queries.I hope to have some working PoC code for PostgreSQL within the next
few days so I can share actual working versions of both sample
recursive graph queries and the "overlay of read-only table" queries I
talked to you about.-----
Hannu
Attachments:
t253274_20001-Refactor-set-operation-group-clauses-to-decouple-fro.patchapplication/x-patch; name=0001-Refactor-set-operation-group-clauses-to-decouple-fro.patchDownload+80-68
0003-Support-UNION-DISTINCT-ON-for-non-recursive-set-oper.patchapplication/x-patch; name=0003-Support-UNION-DISTINCT-ON-for-non-recursive-set-oper.patchDownload+268-53
0004-Support-dynamic-pruning-in-recursive-UNION-DISTINCT-.patchapplication/x-patch; name=0004-Support-dynamic-pruning-in-recursive-UNION-DISTINCT-.patchDownload+275-6
0002-Support-inline-ORDER-BY-in-DISTINCT-ON-for-SELECT-an.patchapplication/x-patch; name=0002-Support-inline-ORDER-BY-in-DISTINCT-ON-for-SELECT-an.patchDownload+459-31
Rebased
Attachments:
t253274_3v2-0001-Refactor-set-operation-group-clauses-to-decouple-fro.patchapplication/x-patch; name=v2-0001-Refactor-set-operation-group-clauses-to-decouple-fro.patchDownload+80-68
v2-0004-Support-dynamic-pruning-in-recursive-UNION-DISTINCT-.patchapplication/x-patch; name=v2-0004-Support-dynamic-pruning-in-recursive-UNION-DISTINCT-.patchDownload+275-6
v2-0003-Support-UNION-DISTINCT-ON-for-non-recursive-set-oper.patchapplication/x-patch; name=v2-0003-Support-UNION-DISTINCT-ON-for-non-recursive-set-oper.patchDownload+268-52
v2-0002-Support-inline-ORDER-BY-in-DISTINCT-ON-for-SELECT-an.patchapplication/x-patch; name=v2-0002-Support-inline-ORDER-BY-in-DISTINCT-ON-for-SELECT-an.patchDownload+459-31
On 01/08/2026 15:08, Hannu Krosing wrote:
Hi Vik
Finally had time to put this SQL Standard Propoasl together.
Please take a quick look and tell me what is missing or wrong and what
the next steps should be.
SQL doesn't actually need DISTINCT ON. Two syntaxes already provide it:
SELECT user_id, status, updated_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at
DESC) as rn
FROM user_statuses
QUALIFY rn = 1
SELECT user_id, status, updated_at
FROM user_statuses
ORDER BY updated_at DESC
FETCH FIRST ALL PARTITIONS BY user_id, 1 ROW ONLY
However, putting the ordering inside the DISTINCT ON is a big
improvement for postgres, imo.
SELECT DISTINCT ON (user_id ORDER BY updated_at DESC)
user_id, status, updated_at
FROM user_statuses
ORDER BY status
We can't get rid of the old way of doing it, but that shouldn't prevent
us from having the new version.
I don't understand what the use case for UNION DISTINCT ON is. Could you
please provide one?
--
Vik Fearing