Using an Alias column in the where clause in PostgreSQL

CTEs, Subqueries & Complex Cases

Utilizing CASE inside CTEs

# Gotta love when you can make logic feel more artsy 
WITH alias_table AS (
  SELECT id,
         CASE
           WHEN condition THEN col1
           ELSE col2
         END AS painted_col
  FROM my_table
)
SELECT * FROM alias_table
WHERE painted_col > 200;


When your alias requires sophisticated logic, employ the CASE statement inside a CTE.

Addressing NULLs with COALESCE

WITH cte AS (
  SELECT id, COALESCE(col1, 0) + COALESCE(col2, 0) AS sum_col FROM my_table
)
SELECT * FROM cte WHERE sum_col > 100;


Combat those pesky NULL values by integrating the COALESCE function into the alias definition.

Optimizing performance through expression duplication

SELECT id, (col1+col2) AS sum_col FROM my_table WHERE (col1+col2) > 100;
# Who said redundancy was a bad thing? 


Lift performance by duplicating the CASE statement or the expression from the SELECT clause right inside the WHERE clause.

Organizing Data and Columns

Observing the order of operations

# Respecting precedence – something my kids need to learn
SELECT * FROM (
  SELECT id, (col1+col2) AS sum_col FROM my_table
) AS alias_table WHERE sum_col > 100 ORDER BY sum_col;


Sort data concerning an alias column by including it in the outer scope.

Using LEFT JOINs with aliases

# Joining but not leaving any column behind
WITH cte AS (
  SELECT a.id, COALESCE(SUM(b.value), 0) AS total_value
  FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id
  GROUP BY a.id
)
SELECT * FROM cte WHERE total_value > 100;


Employ LEFT JOIN whilst avoiding implicit filtering with a standard JOIN.

More Tips, Tricks, and Practical Examples

Avoiding the "Column does not exist" heartbreak

# Face it, SQL doesn't accept rebranding without preparation  
WITH cte AS (
  SELECT id, (col1+col2) AS sum_col FROM my_table
)
SELECT * FROM cte WHERE sum_col > 100;


Prevent the "Column doesn't exist" error by defining the alias in a subquery or CTE before the WHERE clause.

Leveraging on PostgreSQL's extra features

# SQL: PostgreSQL :: Basic: Extra toppings
WITH updated_rows AS (
  UPDATE my_table SET col1 = 0 WHERE col2 < 0 RETURNING id, col1
)
SELECT * FROM updated_rows;


Expand your capabilities with PostgreSQL's WITH and RETURNING in data modification queries.

Taking a bold path: Reusing SELECT expressions

# Because we can! (and it works)
SELECT id, col1 + col2 AS sum_col FROM my_table WHERE col1 + col2 > 100;


Choosing not to use subqueries or CTEs? You can directly reuse SELECT expressions in the WHERE condition.