Using an Alias column in the where clause in Postgresql

by

I struggled on the same issue and “mysql syntax is non-standard” is not a valid argument in my opinion. PostgreSQL adds handy non-standard extensions as well, for example “INSERT … RETURNING …” to get auto ids after inserts. Also, repeating large queries is not an elegant solution.

However, I found the WITH statement very helpful (CTE’s). It sort of creates a temporary view within the query which you can use like a usual table then. I’m not sure if I have rewritten your JOIN correctly, but in general it should work like this:

WITH jobs_refined AS (
    SELECT
        jobs.*,
        (SELECT CASE WHEN lead_informations.state IS NOT NULL THEN lead_informations.state ELSE 'NEW' END) AS lead_state
    FROM jobs
    LEFT JOIN lead_informations
        ON lead_informations.job_id = jobs.id
        AND lead_informations.mechanic_id = 3
)
SELECT *
FROM jobs_refined
WHERE lead_state="NEW"

More Related Contents:

  1. PostgreSQL: using a calculated column in the same query
  2. Why can’t I use column aliases in the next SELECT expression?
  3. Select first row in each GROUP BY group?
  4. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  5. How to use RETURNING with ON CONFLICT in PostgreSQL?
  6. Save PL/pgSQL output from PostgreSQL to a CSV file
  7. Difference between language sql and language plpgsql in PostgreSQL functions
  8. Query with LEFT JOIN not returning rows for count of 0
  9. How to include “zero” / “0” results in COUNT aggregate?
  10. SQL SELECT speed int vs varchar
  11. delete “column does not exist”
  12. How to create a temporary function in PostgreSQL?
  13. Postgres Case Sensitivity
  14. Polymorphism in SQL database tables?
  15. Query for element of array in JSON column
  16. Casting NULL type when updating multiple rows
  17. How to round an average to 2 decimal places in PostgreSQL?
  18. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  19. postgresql: INSERT INTO … (SELECT * …)
  20. Trim trailing spaces with PostgreSQL
  21. INSERT COMMAND :: ERROR: column “value” does not exist
  22. Return multiple columns and rows from a function PostgreSQL instead of record
  23. Postgres error updating column data
  24. SQL LIKE condition to check for integer?
  25. How to detect query which holds the lock in Postgres?
  26. PostgreSQL how to create a copy of a database or schema?
  27. PostgreSQL: fill NULL values in timeserie query with previous value
  28. Postgres: convert single row to multiple rows (unpivot)
  29. Get most common value for each value of another column in SQL
  30. PostgreSQL: top n entries per item in same table

Leave a Comment