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're trying to use column aliases in an expression. If a system allows you to do that it's just syntactic sugar. This should work in any SQL dialect.

select 
 cost_1
,quantity_1
,cost_2
,quantity_2
,cost_1 * quantity_1 as total_1
,cost_2 * quantity_2 as total_2
,(cost_1 * quantity_1) + (cost_2 * quantity_2) as total_3 

from data;

Answer 2

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 3

As a rule, there a two things you need to know about the SELECT clause:

• Although it is written first, it is evaluated last, with the exception of the ORDER BY clause. This is why you cannot use any calculated fields or aliases in any other clause (particularly the WHERE clause) except in the ORDER BY clause.
• Calculations in the SELECT clause are performed in parallel, or at least are handled as if they are. This is why you cannot use one calculation as part of another.

So, the short answer is that you can’t, and that is by design.

The notable exception to this is Microsoft Access, where you can indeed use calculations in subsequent columns and WHERE clauses. However, although that is convenient, it’s not actually an advantage: not following the above principals is less efficient. But it’s OK for light duty databases, which is what Access is supposed to be used for.

If you really want re-use calculated results, you will need a separate query, either in the form of a sub-query or as a Common Table Expression. CTEs are much easier to work with, as they are clearer to read.

Edit

Here is an example why using calculated columns could cause confusion. In Australia we measure height in centimetres, but there still some places which use the ancient inches (1 in = 2.54 cm).

SELECT
    id,
    height/2.54 as height, -- cm -> in
    case when height>175 then 'tall' else '' end as comment
FROM people;

Here the CASE still uses the original height value.

Answer 4

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

Answer 5

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 ║
╚═════════╩═════════════╩═════════╩═════════════╩══════════╩══════════╩═════════╝