FROM clause before SELECT
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:t253212psql -h localhost -U postgresBuilt from patchset v3 (message #3), August 24, 2026 at 05:27 AM.
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 t253212_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 t253212_3 && git checkout t253212_3Patchset v3 (message #3) is on t253212_3
Hi hackers!
At the sessions where I show newcomers around the codebase, we need a
first patch every time: small enough to finish in an hour, in a part of
the DBMSs everyone has an opinion about. The one we usually write makes
this work:
FROM pg_stat_activity SELECT *;
It is a great exercise. You touch the grammar, you learn what bison
means by a conflict, you rebuild, and then the thing actually runs and
prints rows. Then comes my punchline: "of course, this is a non-standard
SQL extension, so it can never go upstream". Everyone nods sadly, and we
move on to something useful.
I have delivered that punchline enough times to start doubting it. The
initial thought is usually to make the grammar more extensible, but that
is a large project without a big win for users. So here is the patch
that I usually throw away, with the parts I usually skip.
0001 lets the FROM clause be written ahead of SELECT, and lets the
SELECT clause be omitted entirely (then it means SELECT *). The other
clauses keep their usual places. It is accepted wherever a SELECT is:
sub-selects, CTEs, set operations, INSERT ... SELECT, and, since FROM is
a reserved word there, in PL/pgSQL statements too. The productions build
exactly the same SelectStmt as the standard spelling, so nothing below
the parser changes and stored queries are displayed the standard way.
There are no new grammar conflicts. DuckDB has had the same syntax for
years and calls it FROM-first [0]https://duckdb.org/docs/stable/sql/query_syntax/from.
0002 is the part that might be the actual pro-argument. In an off-list
chat Tom pointed out that FROM first would make sense for tab
completion, given a completer a lot smarter than psql is today. It turns
out even today's psql gets somewhere useful. Once the tables are named,
it can complete the columns in the select list itself, which it simply
cannot do for a query that starts with SELECT. The completer only ever
sees the text to the left of the cursor, so in "select c1, <tab> from
t2" the table is on your screen but not in psql's hands. It is not a
missing rule, it is the order of the words. Columns in WHERE and ORDER
BY are completed today already; those rules assume the table name is the
word right before the clause, so they need a small adjustment for the
new shape, and GROUP BY gets column completion it never had. So the gain
is one clause wide, but it is the clause you type first.
What I deliberately did not do: accept the section keywords in any order
at all (Vik's suggestion), or allow SELECT last after GROUP BY (Andreas
Karlsson's). Both are defensible, but let's keep the surface minimal
and tied to the completion argument. Also left out: the rest of the
friendly-SQL menu other systems ship, such as ORDER BY ALL, SELECT *
EXCLUDE and trailing commas. Those are separate discussions.
Tests are in select.sql and plpgsql.sql, psql's completion tests grew
five checks, and the SELECT reference page documents the form and calls
it a PostgreSQL extension in Compatibility.
If the consensus is that our grammar should stay standard-only, that is
a perfectly good answer, and I will restore the punchline for the next
session.
...the real motivation for these patches is "just for fun", of course.
Thank you!
Best regards, Andrey Borodin.
Attachments:
0001-Allow-the-FROM-clause-to-be-written-before-the-SELEC.patchapplication/octet-stream; name=0001-Allow-the-FROM-clause-to-be-written-before-the-SELEC.patch; x-unix-mode=0644Download+395-13
0002-psql-complete-column-names-in-FROM-first-queries.patchapplication/octet-stream; name=0002-psql-complete-column-names-in-FROM-first-queries.patch; x-unix-mode=0644Download+67-2
On 27/07/2026 15:30, Andrey Borodin wrote:
Hi hackers!
At the sessions where I show newcomers around the codebase, we need a
first patch every time: small enough to finish in an hour, in a part of
the DBMSs everyone has an opinion about. The one we usually write makes
this work:FROM pg_stat_activity SELECT *;
I am generally in favor of this. (Notice: I haven't read the patch)
What I deliberately did not do: accept the section keywords in any order
at all (Vik's suggestion),
That wasn't really a serious suggestion. Allegedly.
or allow SELECT last after GROUP BY (Andreas
Karlsson's). Both are defensible, but let's keep the surface minimal
and tied to the completion argument.
This is a lot more serious, and if we are going to move the SELECT
clause, I agree with Andreas K. that it belongs after HAVING or possibly
even later.
If the consensus is that our grammar should stay standard-only, that is
a perfectly good answer, and I will restore the punchline for the next
session.
The standard is never going to move the SELECT, so we are free to do so
as an extension to the standard. The main problem is it has become a
mush-mash of random stuff so its proper placement is a bit vague.
--
Vik Fearing
Hi Vik,
Thank you! Here's v2: it is no longer "move FROM to the front", it
is "throw SELECT to the end".
On 27/07/2026 21:00, Vik Fearing wrote:
This is a lot more serious, and if we are going to move the SELECT
clause, I agree with Andreas K. that it belongs after HAVING or
possibly even later.
This turned out to be simpler. To hack and to understand.
FROM films WHERE len > '02:00' GROUP BY kind SELECT kind, count(*)
WHERE, GROUP BY, HAVING and WINDOW keep their order relative to each
other and all come before the select list. ORDER BY, LIMIT and the
locking clauses stay at the end of the query, where they already are.
The SELECT clause may still be left out entirely, in which case it
means SELECT *, so "FROM films" works as before. So does the example I
started with: "FROM pg_stat_activity SELECT *".
Why not SELECT in the middle?
-----------------------------
v1 allowed the select list right after FROM, with the other clauses
trailing it. Accepting that placement along with "FROM in the end" is
doable. It costs a struct, a merge function and an error for a clause
written on both sides, just some grammar code.
What stopped me is not the grammar, but a way to convey the new feature.
We are adding a new way to write queries, and it has to be teachable.
"The select list can come last" is easy to understand. "The select list
may come first, second or last" is not a rule, it is a list of permissions
and I would not enjoy explaining it.
The argument on the other side is compatibility, and it is not weak.
DuckDB [0]https://duckdb.org/docs/stable/sql/query_syntax/from and ClickHouse [1]https://clickhouse.com/docs/reference/statements/select/from both ship v1 placement, so a
query brought from either of them,
FROM films SELECT title WHERE len > '02:00'
works there and fails here. Nobody, as far as I know, ships the
placement in v2-patch grammar.
WDYT?
The standard is never going to move the SELECT, so we are free to do
so as an extension to the standard.
This sentence deserves a place in the commit message.
Thank you!
Best regards, Andrey Borodin.
[0]: https://duckdb.org/docs/stable/sql/query_syntax/from
[1]: https://clickhouse.com/docs/reference/statements/select/from
Hi,
If the main motivation is better completion, with compatibility with
DuckDB and ClickHouse as an additional benefit, v1 seems like the
better choice.
With v1, FROM establishes the relation scope before SELECT, allowing
psql to complete input columns. The select list then defines output
aliases before GROUP BY and ORDER BY, where PostgreSQL permits those
aliases. This gives us another useful completion opportunity. v2 loses
this property for GROUP BY.
v1 also matches the existing FROM-first syntax in DuckDB and
ClickHouse.
I think we should also test completion with JOINs: multiple relations,
table aliases, qualified columns, ON/USING, and target-list columns
from both sides. The current rules seem mostly centered on a single
relation.
Best regards,
Denis Smirnov