Introducing our new eBook: "Best Practices for Optimizing Postgres Query Performance"Download Now

U115: Subquery in FROM must have an alias

Category: Application / User Errors
SQLSTATE: 42601 (Class 42 — Syntax Error or Access Rule Violation: syntax_error)
Urgency: low

Example Postgres Log Output:

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

Explanation:

This log event is closely related to a syntax error, but its a distinct issue that many people run into, as its not immediately obvious that queries need to be written this way.

For example, the following query will produce this error:

SELECT * FROM (SELECT uid, COUNT(*) AS count FROM my_table GROUP BY 1 ORDER BY 2) LIMIT 1;

The correct fix is to give the subquery in the FROM clause an alias (i.e. a name), so that it can be uniquely identified in the query. You have to do this even if you are not going to reference the name anywhere, like this:

SELECT * FROM (SELECT uid, COUNT(*) AS count FROM my_table GROUP BY 1 ORDER BY 2) AS x LIMIT 1;

This error usually occurs in development or test environments, as queries written like this will always error out when run, independent of the query plan or values passed in.

Recommended Action:

Add an alias name of your choice to the subquery in the SQL thats generated by your application.

Learn More:


Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →