FROM CLAUSE ALIASES, SUB-SELECTS IN 

Article

SELECT r.rolname, s.usesuper, sub.datname, val.s FROM pg_roles AS r, pg_shadow AS s, LATERAL (SELECT datname FROM pg_database d WHERE d.datdba = r.oid) AS sub, (VALUES (10, 'foo'), (13, 'bar')) AS val(i, s) WHERE r.oid = val.i; 

alias definition rules 

It’s ironic that frequent references to tables, views, and functions cannot be made via an alias. However, we are required by the SQL standard to use aliases for sub-SELECTS and VALUES clauses. And for many years, PostgreSQL adhered rigidly to this rule:

SELECT * FROM (SELECT generate_series(1, 3)), (VALUES (10, 'foo'), (13, 'bar')); SQL Error [42601]: ERROR: subquery in FROM must have an alias Hint: For example, FROM (SELECT ...) [AS] foo.

I occasionally run into people who wonder why the alias in the FROM clause for subqueries in PostgreSQL is required, especially during Oracle to PostgreSQL migration efforts. The SQL standard mandates it, which is the default response here.

The note on this topic in our parser also exactly reads as follows:

A subselect () without an alias clause is not allowed by the SQL spec, thus neither do we. By doing this, the issue of having to create a special refname for it is solved. If there is enough public demand, that may be overcome, but for now, let’s just implement the specification and see if anyone objects. It does, however, seem like a good idea to emit a nicer error message than “syntax error.” /s… 

Bernd immediately offered a fix, but it was turned down because there wasn’t agreement on the solution.

According to what I gather, there was no agreement at the time regarding the significance of removing this rule. So, rather than being a technical inquiry, this one was more political. And PostgreSQL adhered to the norm for five long years. Additionally, the future v15 major release will continue to do so.

The subsequent PostgreSQL major release should make subquery aliases optional.

However, Dean Rasheed recently published a new contribution to the development branch with the following request: Make subquery aliases in the FROM clause optional. 

This enables the FROM clause’s VALUES and sub-SELECT aliases to be omitted.

This is an extension of the SQL standard, which is supported by a few other database management systems. It makes the transfer from those systems easier and does away with the small nuisance that comes with the need for these aliases.

Tom Lane has reviewed my patch.

CAEZATCUCGCf82=hxd9N5n6xGHPyYpQnxW8HneeH+uP7yNALkWA@mail.gmail.com

I advise you to look over those patches, which were both contributed by Dean and Bernd, to get a sense of the many approaches that might be taken to the same issue. In other words, Dean’s technique does not generate an alias at all, which appears neater and simpler and requires less code than trying to build a unique alias. Bernd’s approach was to automatically construct unique aliases for clauses.

Let’s see if that functions. To do that, I created PostgreSQL from scratch and executed the aforementioned test query:

test=# SELECT version(); version ---------------------------------------------------------------------------------------------------------- PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit (1 row) test=# SELECT * FROM (SELECT generate_series(1, 3)), (VALUES (10, 'foo'), (13, 'bar')); generate_series | column1 | column2 -----------------+---------+--------- 1 | 10 | foo 1 | 13 | bar 2 | 10 | foo 2 | 13 | bar 3 | 10 | foo 3 | 13 | bar (6 rows)

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​