5mins of Postgres E27: Postgres 16 - Make subquery alias optional in FROM clause

By Lukas Fittl

July 21, 2022

Today, we are talking about a change in Postgres 16, that removes the alias requirement for subqueries in FROM.

This may seem like a very obscure problem, but it's actually a very common issue for people that either migrate to Postgres, for example from Oracle to Postgres, or who are new to Postgres and run into this error.

Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel.

Fixing the "subquery in FROM must have an alias" error
The first attempt to fix the error in 2017, to help migrating Oracle to Postgres
A new patch in Postgres 16
Trying out the development branch locally
What we have discussed in this episode of 5mins of Postgres

Transcript

Fixing the "subquery in FROM must have an alias" error

Today you have this error subquery in FROM must have an alias:

ERROR: subquery in FROM must have an alias at character 15
HINT: For example, FROM (SELECT ...) [AS] foo.
STATEMENT: SELECT * FROM (SELECT 1)

This happens when you have your SELECT statement, you have a FROM clause, and then at the end of the FROM clause, you forget to specify a name for the sub-SELECT in the FROM clause.

SELECT * FROM (SELECT * FROM mytable);

The solution today is to add a name: You have your FROM clause and you say this sub-SELECT is named "somethingSomethingTable", this way today you can resolve this error.

SELECT * FROM (SELECT * FROM mytable) AS myalias;

A lot of people have run into this. This StackOverflow question has been viewed 217,000 times. And as people note here, on this about 10 year old StackOverflow post, this is still an issue with Postgres 11 and it's actually still a problem with Postgres 15.

The first attempt to fix the error in 2017, to help migrating Oracle to Postgres

Other people in the Postgres community have wondered why this is the case. Back in 2017 Bernd Helmle opened up a mailing list thread on the Postgres hackers mailing list. He brought this up, because in the context of migration projects from Oracle to Postgres, he's repeatedly faced with people asking "Why is this this way?", and typically the Postgres community has said, "this is because the SQL standard requires it".

If you look at the Postgres source code, you can actually see that this is also the explanation in the parser, it says the SQL spec does not permit a sub-select without an ALIAS clause, so Postgres doesn't either:

/*
 * The SQL spec does not permit a subselect
 * (<derived_table>) 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".
 */

And then it also says, "see if anyone complains".

As you can see here, and as you can see in the StackOverflow post, there have actually been a good amount of complaints, but they haven't really made it to the Postgres core community. What Bernd did here is he brought this concern to the community.

Now back in 2017, this mailing list thread went on and on, there was a patch, but there were some concerns around how to name the alias and such. It actually ended back in 2017, it didn't really make progress there. Somebody else also noted that this is not just an issue with Oracle, this is also an issue with SQLite. SQLite is also more lenient, even though the SQL standard requires this alias.

A new patch in Postgres 16

About four weeks ago, Dean Rasheed picked up this topic again for Postgres 16, he referenced this earlier thread, and he developed a new patch that didn't have the same problems as the old patch. The good thing is that, ultimately, after some discussion, this patch actually got merged into Postgres 16:

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.

Again, to be clear, this is for 16, not for 15. We'll have to wait a little while to get this improvement here, but this is going to make things a lot easier for newcomers.

Trying out the development branch locally

I'm excited by this change and I wanted to try this out locally. First of all, here's a Postgres 14 instance. If you had a Postgres 15 beta instance, it would behave the same way. I can create a table, and then I'll try to select something from the table through a sub-SELECT. You can see here, this is the error that you get today in Postgres, where it says "subquery in FROM must have an alias" and it hints at how to fix it. For example, here, if I follow this hint, then it works.

Now, I also built Postgres 16. Don't do this at home. Don't use this for production. This is just for test purposes. I built the latest version of the main branch here locally, and you can see that it says "16devel". We'll do the exact same steps, I'll do a CREATE TABLE, and then I'll do a sub-SELECT:

psql (16devel)
Type "help" for help.

postgres=# CREATE TABLE test(id int);
CREATE TABLE
postgres=# SELECT * FROM (SELECT * FROM test);
id
----
(0 rows)

And you can see here, this works. I can still specify an alias if I want to, but it's not required, and I think this will avoid a lot of errors.

There wasn't really a good reason for this error besides standards compliance. This shows that it's sometimes good to go beyond the standard and do what other systems also realize, to make it more user-friendly.

Very excited about Postgres 16, we'll have to wait a little bit over a year to get this change, and it may still change because it's early in the development cycle, but hopefully we'll see this in new Postgres versions.

Thank you so much for listening. This was 5mins of Postgres. Subscribe to our YouTube channel or follow us on Twitter to hear about next week's episodes. Talk to you next week!