PostgreSQL does not accept column alias in WHERE clause

by

You ask two questions:
1.

Why can’t I refer to the SELECT cost alias at the WHERE clause?

2.

But why order by cost desc; is allowed?

The manual has an answer for both of them here:

An output column’s name can be used to refer to the column’s value in
ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING
clauses; there you must write out the expression instead.

It’s defined by the SQL standard and the reason is the sequence of events in a SELECT query. At the time WHERE clauses are applied, output columns in the SELECT list have not yet been computed. But when it comes to ORDER BY, output columns are readily available.

So while this is inconvenient and confusing at first, it still kind of makes sense.

Related:

More Related Contents:

  1. Select rows which are not present in other table
  2. Optimize GROUP BY query to retrieve latest row per user
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Aggregate columns with additional (distinct) filters
  5. Check if value exists in Postgres array
  6. Insert data in 3 tables at a time using Postgres
  7. How to use ANY instead of IN in a WHERE clause?
  8. Is there a shortcut for SELECT * FROM?
  9. sql statement error: “column .. does not exist”
  10. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  11. sql ORDER BY multiple values in specific order?
  12. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  13. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  14. PostgreSQL: Which Datatype should be used for Currency?
  15. Sorting null values after all others, except special
  16. Reference alias in WHERE clause
  17. NOT NULL constraint over a set of columns
  18. ERROR: functions in index expression must be marked IMMUTABLE in Postgres
  19. Why postgres returns unordered data in select query, after updation of row?
  20. postgresql – sql – count of `true` values
  21. Continuing a transaction after primary key violation error
  22. Transposing an sql result so that one column goes onto multiple columns
  23. SQL column reference “id” is ambiguous
  24. How to use multiple CTEs in a single SQL query?
  25. IN Clause with NULL or IS NULL
  26. postgres – where in (list) – column does not exist
  27. How to get a list column names and datatypes of a table in PostgreSQL?
  28. Postgresql query between date ranges
  29. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  30. Why isn’t row level security enabled for Postgres views?

Leave a Comment