(opens in a new tab)

Subquery in FROM Must Have an Alias: Postgres

November 8, 2026 by Chat2DB Team

A query that works on your laptop fails on the production server:

SELECT count(*) FROM (SELECT 1 AS x UNION ALL SELECT 2);

ERROR:  subquery in FROM must have an alias
LINE 1: select count(*) from (select 1 as x union all select 2);
                             ^
HINT:  For example, FROM (SELECT ...) [AS] foo.

The fix is one word: give the subquery a name. The more interesting question is why the same query runs on one server and fails on another. The answer is the PostgreSQL version. Versions up to 15 require the alias, and versions 16 and newer do not. We ran every example below on PostgreSQL 15.19 and 18.6.

The Quick Fix

Add an alias after the closing parenthesis. AS is optional:

SELECT count(*) FROM (SELECT 1 AS x UNION ALL SELECT 2) AS t;
SELECT count(*) FROM (SELECT 1 AS x UNION ALL SELECT 2) t;

Both forms work on every PostgreSQL version. The name only has to be unique within that level of the query. Many people use t, sub or a name that says what the subquery returns, such as daily_totals.

Why It Works on PostgreSQL 16 and Newer

Up to version 15, PostgreSQL required a name for every derived table, as the SQL standard did. The PostgreSQL 16 release notes list the change plainly: subqueries in the FROM clause may now omit aliases. On 18 the query from the top of this article simply runs:

 count
-------
     2
(1 row)

So if your development machine runs 16, 17 or 18 and your production server runs 15 or older, queries without aliases pass every local test and then fail in production. This happens a lot right now, because many teams are moving off PostgreSQL 14, which reaches end of life on November 12, 2026, and their development and production servers are on different versions for a while.

Check the server version from SQL if you are not sure which one you are talking to:

SHOW server_version;

Even on versions that allow it, a missing alias makes a query harder to read. The outer query can only refer to the subquery's columns by their bare names, and anyone reading the query has to work out which subquery each column comes from. An alias makes that explicit.

Every Place the Error Shows Up

The rule covers every subquery in a FROM clause, not only the first one. On 15, all of these fail with the same message.

A subquery joined to a named table:

SELECT * FROM (SELECT 1 AS x) AS t JOIN (SELECT 1 AS y) ON true;

ERROR:  subquery in FROM must have an alias
LINE 1: select * from (select 1 as x) as t join (select 1 as y) on t...
                                                ^

The caret points at the second subquery, which is the one without a name.

A subquery next to a function call in a comma-separated list:

SELECT * FROM generate_series(1, 2), (SELECT 3);

A VALUES list used as a table has its own variant of the message:

SELECT * FROM (VALUES (1, 'a'), (2, 'b'));

ERROR:  VALUES in FROM must have an alias
HINT:  For example, FROM (VALUES ...) [AS] foo.

The fix is the same. With VALUES, also name the columns, because otherwise they are called column1, column2 and so on:

SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS v(id, code);

Naming Columns in the Alias

An alias can rename the subquery's columns too. This is useful when the subquery produces expressions without names:

SELECT a FROM (SELECT 1 AS x) AS t(a);

 a
---
 1

The column list maps by position. If the list is shorter than the number of columns, the remaining columns keep their own names. If it is longer, PostgreSQL reports an error. Use this form when you build queries on top of UNION results or VALUES lists, because it keeps the outer query readable.

LATERAL Subqueries Need an Alias Too

A LATERAL subquery can refer to columns of the tables listed before it, which makes it handy for per-row calculations and "top N per group" queries. It is still a subquery in FROM, so the same rule applies. On 15:

SELECT * FROM generate_series(1, 2) g, LATERAL (SELECT g * 10);

ERROR:  subquery in FROM must have an alias
LINE 1: select * from generate_series(1,2) g, lateral (select g * 10...
                                                      ^

On 18 the query runs, and the computed column comes back with the generic name ?column?. Naming both the subquery and its column fixes the error on 15 and gives the result a usable column name on every version:

SELECT * FROM generate_series(1, 2) g, LATERAL (SELECT g * 10 AS x) AS l;

 g | x
---+----
 1 | 10
 2 | 20

The same goes for a LATERAL subquery inside a JOIN ... ON true, which is the usual way to write a top-N-per-group query. Put the alias right after the closing parenthesis, before ON.

What PostgreSQL 16 Does Without an Alias

On 16 and newer, a subquery without an alias still needs some internal name. PostgreSQL gives it one, unnamed_subquery, but you cannot use that name in your query:

SELECT unnamed_subquery.x FROM (SELECT 1 AS x);

ERROR:  invalid reference to FROM-clause entry for table "unnamed_subquery"
DETAIL:  There is an entry for table "unnamed_subquery", but it cannot be referenced from this part of the query.

Unqualified column names still work. The practical limit shows up with two unnamed subqueries that have a column with the same name. A bare x is then ambiguous, and you cannot qualify it. Name the subqueries and the problem goes away. That is a good reason to keep writing aliases even on 18: they make joins between subqueries possible to read and to qualify.

Common Sources of the Error

The error rarely comes from hand-written SQL, because most people alias subqueries out of habit. It comes from somewhere else:

• Queries copied from MySQL. MySQL has the same rule, with the message Every derived table must have its own alias (error 1248). Code written for MySQL 8.0 usually has aliases already, but snippets from blogs often do not. Our MySQL 1248 guide covers that side.
• Queries written against a newer PostgreSQL. As described above, code developed on 16+ can lack aliases and break on older production servers.
• Generated SQL. ORMs and query builders usually add aliases, but hand-assembled SQL strings in application code often do not. If the generated query wraps another query, for example for pagination with SELECT count(*) FROM (...), check that the wrapper adds a name.
• AI-generated SQL. Language models trained on recent PostgreSQL or on other databases may leave out the alias. Run generated queries against the same major version as production before you ship them. In Chat2DB (opens in a new tab), the AI assistant writes queries against your real connection, and you can run them right away on that server to catch this kind of version difference.

Make Your Queries Portable

If your code has to run on several PostgreSQL versions, or on several databases, follow three habits:

1. Always alias derived tables, even where the server does not require it. It costs nothing and works everywhere.
2. Qualify columns with the alias in joins, for example t.customer_id, so later changes to either side cannot make a column ambiguous.
3. Test on the oldest version you support. A CI job that runs your migrations and queries against the oldest production version catches this error and others like it, such as functions or syntax that only exist in newer releases.

Here is a typical reporting query written to work on any version:

SELECT d.day, d.orders, d.revenue
FROM (
    SELECT date_trunc('day', created) AS day,
           count(*)                   AS orders,
           sum(amount)                AS revenue
    FROM orders
    GROUP BY 1
) AS d
WHERE d.orders > 100
ORDER BY d.day;

The subquery is named d, and every column in the outer query is qualified with it.

Finding Unaliased Subqueries Before They Reach Production

If you are about to run code written on 16+ against an older server, it pays to find these queries up front instead of one error at a time. A text search catches many of them. Look for FROM ( or JOIN ( where the matching closing parenthesis is followed directly by WHERE, ON, GROUP, ORDER, LIMIT, a comma, a semicolon or the end of the query. Such patterns are hard to match reliably with a regular expression, because subqueries nest. Treat the search as a first pass, not a proof.

The reliable check is to run the statements against the older version. If you have a test suite, point it at a database on the oldest supported version. If you do not, at least run EXPLAIN on each query from your query log or ORM output against that server. The alias check happens while the query is parsed, so EXPLAIN reports the error without executing anything or touching data.

Related Errors

If adding the alias exposes another problem, these guides may help: column reference is ambiguous, which appears when two joined subqueries share a column name, and column must appear in the GROUP BY clause, which often comes with aggregate subqueries like the one above.