Aliases for sub-SELECTS in FROM clause

08.2022 / Category: News / Tags: what's new

What are aliases in SQL?

SQL aliases in FROM clauses are used to give a result set a temporary name. A result set may be produced by a table or view name, sub-SELECT and/or VALUES clause. An alias is created with the AS keyword, but the grammar allows us to omit it. An alias only exists for the duration of that query and is often used to make result set and column names more readable.

Table of Contents

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;

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;

In this useless piece of SQL code we have 4 alieses: r, s, sub and val. For the last result set we not only use the name for it but also defined the aliases for its columns: i and s.

Rules to define aliases

The funny thing here is that one may not use an alias for regular tables, views, and function references. But according to the SQL standard, we are obliged to use aliases for sub-SELECTS and VALUES clauses. And PostgreSQL strictly followed this rule for decades:

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.

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.

This wasn't a big problem until PostgreSQL became a popular database and a lot of people started migration from Oracle and other RDBMS. Because in Oracle syntax it wasn't mandatory to use aliases for such clauses. This situation was spotted by Bernd Helmle for the first time 5 years ago:
From time to time, especially during migration projects from Oracle to PostgreSQL, i'm faced with people questioning why the alias in the FROM clause for subqueries in PostgreSQL is mandatory. The default answer here is, the SQL standard requires it.


This also is exactly the comment in our parser about this topic:

/* * 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". */ ...

Bernd provided a patch immediately but it was rejected due to the lack of consensus on the solution.

My understanding is at that time there were no consensus on the importance of getting rid of this rule. So this question was rather a political and not a technical one. And for five long years, PostgreSQL followed the rule. And will still follow in the upcoming v15 major release.

Make subquery aliases optional in the next PostgreSQL major release

But recently a new commit pushed by Dean Rasheed to the development branch: 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

I suggest you examine those patches, both provided by Bernd and Dean, to get an idea about how the very same problem can be addressed differently. In a few words, Bernd's approach was to generate unique aliases for clauses automatically, and Dean's approach is not generating an alias at all, which seems to be neater and simpler, and less code than trying to generate a unique alias.

Let's check if that works. In order to do that, I built PostgreSQL from the source and ran the following 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)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

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)

Yay! It works!

Conclusion

Thanks to this feature, PostgreSQL will be more compatible with Oracle syntax which will ease migration a lot. But if you really want to have a smooth Oracle to PostgreSQL migration experience, give our enhanced Cybertec Migrator a chance! You may start with watching demo videos and later download it and test it on your environment.