Postgresql: using a calculated column in the same query

sql

join

performance

best-practices

byAlex Kataev·Jan 8, 2025

To reference calculated columns within the same PostgreSQL query, you'd use either a subquery or CTE (Common Table Expression). For readability and reusability, the CTE tends to be more favorable. Let's see it in action:

WITH calc AS (SELECT id, (column1 + column2) AS sum FROM your_table)
SELECT id, sum, (sum * 2) AS double_sum FROM calc;

What just happened? The calculated sum was contained in a CTE christened calc, which later selects sum alongside double_sum—the sum doubled—in one smooth query.

Work it: The LATERAL clause

Another strategy to explore is the use of a LATERAL join in PostgreSQL. This tool lets us reference columns from items preceding in the FROM clause—which basically means, the correlation is strong with this one!

SELECT a, b, x.total_3
FROM your_table,
LATERAL (SELECT (a + b) AS total_3) AS x; -- LATERAL is the Yoda of joins

Above, LATERAL calculates total_3 using the columns a and b scooped from your_table directly in the FROM clause of the parent query.

Performance: Is that your final answer?

There's a myth that employing derived tables or CTEs could lead to performance penalties. But listen, brother, PostgreSQL isn't afraid of a little weight lifting. The built-in optimizer handles these scripts just like it would a regular query.

Mind the pitfalls: Column aliases

Spare a thought for column aliases—they don't serve well in WHERE, GROUP BY, or HAVING clauses due to SQL's logical query processing phase order. The likes of PostgreSQL evaluate mentioned clauses before processing the SELECT phase where aliases are named.

Quick fix: Nested subqueries

If you're dealing with a filter or aggregation predicament based on a calculated column, encompass your calculations in a subquery. The outer query will then treat your calculation results as normal table columns. Magic.

SELECT * FROM (
  SELECT id, (column1 + column2) AS total
  FROM your_table
) AS subquery
WHERE total > 10; -- "Spare change for a subquery?"

Indexing: Your secret weapon

Don't let indexing be an afterthought when dealing with calculated columns. If these calculations commonly show up in filters or joins, consider using functional indexes for optimization:

CREATE INDEX idx_calculated_column ON your_table ((column1 + column2)); -- "From zero to hero"

This index can catapult queries using the sum of column1 and column2 to new speeds even though they might not exist as physical columns in the table.

explain-codes / Sql / Postgresql: using a calculated column in the same query

Linked

How to use a calculated column to calculate another column in the same view



How do I use alias in where clause?



Subquery in FROM must have an alias



Select * from subquery



What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?

