Fix "subquery in FROM must have an alias" in PostgreSQL
ERROR: subquery in FROM must have an alias
A subquery in FROM acts like a temporary table, and older Postgres versions insist that every table has a name. Add a short alias after the closing parenthesis. Postgres 16 and newer (including this site) accept the subquery without one, but naming it is still good practice.
PostgreSQL 16 and newer (including the editor on this page) accept these queries, so you'll only see this error on version 15 or older. The fixed versions work everywhere.
Why it happens
- Writing FROM (SELECT ...) without a name after the closing parenthesis.
- Copying a query from a database or Postgres version that doesn't require the alias.
- Wrapping a query to filter on a window function or aggregate and forgetting to name the wrapper.
How to fix it
1. Name the subquery
Fails on PostgreSQL 15 and older
SELECT AVG(order_count) AS avg_orders FROM ( SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id );
Works
SELECT AVG(order_count) AS avg_orders FROM ( SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ) AS per_customer;
AS is optional, so ) per_customer works too. Any name will do, as long as it's not already used in the query. On this site's Postgres 18 the first query runs as is; Postgres 15 and older reject it.
2. Name it so you can join it
Fails on PostgreSQL 15 and older
SELECT c.name, order_count FROM customers c JOIN ( SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ) ON customer_id = c.id;
Works
SELECT c.name, t.order_count FROM customers c JOIN ( SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ) AS t ON t.customer_id = c.id;
With an alias you can write t.order_count, which stays clear even when both sides have columns with the same name. A CTE (WITH t AS (...)) is another clean way to name it.
Try it yourself
This runs real PostgreSQL in your browser. Run the fixed query, then change it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Practice so it doesn't happen in an interview
- Customers by number of ordersAggregate of an aggregate · LEFT JOIN · COUNT(column)
- Consecutive-day loginsLAG() · date arithmetic
- Customers with a big orderIN (subquery) · EXISTS