42601 Syntax Error

You may encounter an error message like following.

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

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.

Solution:

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