99

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.

2

5 Answers 5

83

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)

4
  • 6
    The original post looks like SAS's PROC SQL which does allow this
    – Andrew
    Commented Jun 17, 2015 at 21:07
  • I'd like to have a let ... = .... definition as in Haskell here to define a local calculation instead of a subquery. That would be great. Nevertheless, Thank you for your good answer! Commented Mar 10, 2016 at 23:16
  • What's the advantage/disadvantage of wrapping select statement and not using CTE for example? Thanks :)
    – Davita
    Commented May 20, 2016 at 9:52
  • 4
    @Davita: in Postgres a CTE is optimized differently then a derived table. In most cases (including this one) this doesn't make a difference, so it boils down to personal preferences. Also not everybody is used to CTEs and that would have introduced another level of "complexity" in the answer which I wanted to avoid.
    – user330315
    Commented May 20, 2016 at 9:54
75

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 ║
╚═════════╩═════════════╩═════════╩═════════════╩══════════╩══════════╩═════════╝
3
  • 4
    Oh nice. TIL. This is so much nicer for some cases, including mine which is creating a view that does many layers of reference to previous calculations. In-lining repeated calculations is error-prone and nesting subqueries had like 9 levels of nesting for me. The LATERAL version is much easier to follow and maintain.
    – Bo Jeanes
    Commented Mar 16, 2017 at 1:09
  • 1
    What is the s1 in the lateral part here? By trial and error I've found that it can be arbitrary string - why is that required then?
    – redacted
    Commented Jun 17, 2019 at 10:28
  • 2
    @RobinNemeth s1 is alias for derived table. You could use it in SELECT like SELECT s1.total_1 + s1.total_2 AS total_3 Commented Jun 17, 2019 at 14:22
34

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.

1
  • As an aside, whenever I am dealing with fields with physical units, I always include the unit, where possible in the schema, but if I'm building some complicated queries, I would alias height as height_cm as an example. It's less verbose but always helpful whenever you may be needing to do that conversion between metric and imperial. Commented Jan 8, 2020 at 20:44
-1
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;
2
  • Not in case of postgres! Commented Dec 7, 2018 at 8:02
  • it does not work for sure, not in postgresql Commented Apr 19, 2022 at 19:11
-5

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;
1
  • Not in case of postgres! Commented Dec 7, 2018 at 8:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.