PostgreSQL: using a calculated column in the same query

Question

I am having trouble using a calculated column in postgres. A similar code which works in SQL is given below, is it possible to recreate this in PostgreSQL?

select cost_1, quantity_1, cost_2, quantity_2,        (cost_1 * quantity_1) as total_1,       (cost_2 * quantity_2) as total_2,       (calculated total_1 + calculated total_2) as total_3 from data; 

In PostgreSQL a similar code returns the error that:

the column total_1 and total_2 do not exist.

Answer 1

You need to wrap the SELECT statement into a derived table in order to be able to access the column alias:

select cost1,        quantity_1,        cost_2,        quantity_2        total_1 + total_2 as total_3 from (     select cost_1,             quantity_1,             cost_2,             quantity_2,             (cost_1 * quantity_1) as total_1,            (cost_2 * quantity_2) as total_2     from data ) t 

There won't be any performance penalty on that.

(I'm really surprised that your original SQL statement runs at all in a DBMS)

Answer 2

If you don't like wraping entire query with outerquery, you could use LATERAL to calculate intermediate total_1 and total_2:

SELECT cost_1, quantity_1, cost_2, quantity_2, total_1, total_2,        total_1 + total_2 AS total_3 FROM data ,LATERAL(SELECT cost_1 * quantity_1, cost_2 * quantity_2) AS s1(total_1,total_2); 

DBFiddle Demo

Output:

╔═════════╦═════════════╦═════════╦═════════════╦══════════╦══════════╦═════════╗ ║ cost_1  ║ quantity_1  ║ cost_2  ║ quantity_2  ║ total_1  ║ total_2  ║ total_3 ║ ╠═════════╬═════════════╬═════════╬═════════════╬══════════╬══════════╬═════════╣ ║      1  ║          2  ║      3  ║          4  ║       2  ║      12  ║      14 ║ ║      3  ║          5  ║      7  ║          9  ║      15  ║      63  ║      78 ║ ║     10  ║          5  ║     20  ║          2  ║      50  ║      40  ║      90 ║ ╚═════════╩═════════════╩═════════╩═════════════╩══════════╩══════════╩═════════╝