SQL lets you give a result set a temporary name through an alias in the FROM clause. The result set itself can come from a table or view name, a sub-SELECT, or a VALUES clause. Aliases are written with the AS keyword, though the grammar permits omitting it. An alias lives only for the duration of the query and is typically used to make result set and column names more readable.

1

2

3

4

5

6

7

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;

That snippet carries four aliases: r, s, sub and val. The last result set is not only named — its columns are aliased too, as i and s.

Aliases are optional for tables, mandatory for subqueries

Regular tables, views and function references never required an alias. The SQL standard does, however, oblige you to supply one for sub-SELECT and VALUES clauses, and PostgreSQL enforced that rule strictly for decades.

1

2

3

4

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.

For a long time this was a minor annoyance rather than a real problem. It became more visible as PostgreSQL grew popular and users began migrating from Oracle and other RDBMS, where such aliases were never mandatory. Bernd Helmle raised the issue five years ago, describing users in migration projects asking why an alias in the FROM clause for subqueries is mandatory in PostgreSQL, with the standard being the usual answer. That is also what the parser comment on the topic says.

1

2

3

/* * The SQL spec does not permit a subselect * () without an alias clause, * so we don't either. This avoids the problem * of needing to invent a unique

refname for it. * That could be surmounted if there's sufficient * popular demand, but for now let's just implement * the spec and see if anyone complains. *

However, it does seem like a good idea to emit * an error message that's better than "syntax error". */

Helmle submitted a patch right away, but it was rejected because no consensus existed on how to solve the problem. At the time, the question of whether to drop the rule was political rather than technical. Five years later the rule was still in force, and remained so in the upcoming v15 major release.

The commit that changes it

Dean Rasheed has now pushed a commit to the development branch titled Make subquery aliases optional in the FROM clause:

This allows aliases for sub-SELECTs and VALUES clauses in the FROM clause to be omitted.

This is an extension of the SQL standard, supported by some other database systems, and so eases the transition from such systems, as well as removing the minor inconvenience caused by requiring these aliases.

Patch by me, reviewed by Tom Lane.

Discussion: https://postgr.es/m/CAEZATCUCGCf82=hxd9N5n6xGHPyYpQnxW8HneeH+uP7yNALkWA@mail.gmail.com

Comparing the two patches shows how one problem can be addressed differently. Helmle's approach generated unique aliases automatically for such clauses. Rasheed's generates no alias at all, which is neater and simpler and needs less code than manufacturing a unique name.

To verify the behavior, PostgreSQL was built from source and the following query was run:

1

2

3

4

5

6

SELECT version();

                              version

----------------------------------------------------------------------------------------------------------

PostgreSQL 18.2 on x86_64-apple-darwin25.3.0, compiled by Apple clang version 17.0.0 (clang-1700.6.3.2), 64-bit

(1 row)

1

2

3

4

5

6

7

8

9

10

11

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)

Which it now does.

The net effect is closer compatibility with Oracle syntax, which substantially eases migration. Anyone planning an Oracle to PostgreSQL move can also look at Cybertec Migrator, starting with the demo videos and then downloading it to test in their own environment.