Postgresql: Using a Calculated Column in the Same Query

reuse a calculated field in the same query

Column aliases cannot be re-used in the SELECT where they are defined -- and for a simple reason. MySQL (in particular) and SQL in general does not guarantee the order of evaluation of expressions in the SELECT.

In your case, the simplest solution is to repeat the expression, because it is so simple.

You have another problem in your query, though. You are aggregating by banca but only selecting nome.

Here is a better way to write the query:

SELECT c.nome, sum(?.amount) AS total,
       (1500 - sum(?.amount)) AS residuo 
FROM movimenti_carta mc JOIN
     carte c
     ON mc.banca = c.id 
WHERE ?.data >= '2019-05-01' AND
      ?.data < '2019-06-01' 
GROUP by c.nome;

Note the changes:

• All column references should be qualified. The ? is for the alias for the table where the column comes from.
• Use table aliases, which are abbreviations of the table names.
• The unaggregated columns in the SELECT are in the GROUP BY.
• The date arithmetic works for both dates and date/time values.

Reuse calculated column in WHERE clause

There is no way to reuse the calculated field on the same level SELECT. You will need to nest it in order to use the alias.

SELECT field1
    , calc_field
FROM (
    SELECT field1
        , CONCAT (field2, field3) AS calc_field
    FROM MyTable
    ) tbl
WHERE calc_field LIKE 'A%'

This is because of the order in which clauses are executed in a SQL query. As you can see in the way the clauses are listed, the SELECT clause, where the alias is generated, is executed after the WHERE clause.

Thus, the alias is not "visible" in the WHERE clause, because the alias is generated after the WHERE is applied.

Re-using computed columns in the same query for other calculation

The hint tells you what to do: give the sub-query an alias:

SELECT session_id
    , gpstime
    , lat
    , lon
    , track AS heading
    , speed
    , AVG(a1) AS acceleration
FROM (
        SELECT *
            ,((LEAD(speed) OVER (ORDER BY gpstime)) - speed) / 
               (((LEAD(gpstime) OVER (ORDER BY gpstime)) - gpstime) +0.001) AS a2
        , (speed - (LAG(speed) OVER (ORDER BY gpstime))) / 
                     (gpstime - (LAG(gpstime) OVER (ORDER BY gpstime)) + 0.001) AS a1
        FROM my_table
    ) as sub; --<< HERE

But AVG(a1, a2) is also wrong.

avg() is an aggregate function that works on a single column and separate rows. So you either need to use avg(a1) or avg(a2) or maybe you want (a1 + a2) / 2 if you want the average between the two values.

Demo

Postgres: If we select a computed column multiple times, will Postgres compute it again and again?

I am fairly certain that in a case expression, the when clause is going to be evaluated separately for each condition. That means that your colleague is correct . . . probably.

The operative part of the documentation is:

Each condition is an expression that returns a boolean result. If the condition's result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed.

It is possible that Postgres does do some sort of optimization of subexpressions by evaluating them once. However, the statement: "the remainder of the CASE expression is not processed" is pretty strong and suggests that each clause will only be processed after the previous ones have evaluated to false (or NULL).

Regardless of whether the optimizer picks figures out that a function can be called only once, the lateral join guarantees that it will be evaluated once, so that seems like the safer solution for an expensive operation.

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

You could use a nested query:

Select
  ColumnA,
  ColumnB,
  calccolumn1,
  calccolumn1 / ColumnC as calccolumn2
From (
  Select
    ColumnA,
    ColumnB,
    ColumnC,
    ColumnA + ColumnB As calccolumn1
  from t42
);

With a row with values 3, 4, 5 that gives:

   COLUMNA    COLUMNB CALCCOLUMN1 CALCCOLUMN2
---------- ---------- ----------- -----------
         3          4           7         1.4

You can also just repeat the first calculation, unless it's really doing something expensive (via a function call, say):

Select
  ColumnA,
  ColumnB,
  ColumnA + ColumnB As calccolumn1,
  (ColumnA + ColumnB) / ColumnC As calccolumn2
from t42; 

   COLUMNA    COLUMNB CALCCOLUMN1 CALCCOLUMN2
---------- ---------- ----------- -----------
         3          4           7         1.4 

Postgres how to implement calculated column with clause

If you don't want to repeat the expression, you can use a derived table:

select *
from (
   select id, cos(id) + cos(id) as op 
   from myTable 
) as t 
WHERE op > 1;

This won't have any impact on the performance, it is merely syntactic sugar required by the SQL standard.

Alternatively you could rewrite the above to a common table expression:

with t as (
  select id, cos(id) + cos(id) as op 
  from myTable 
)
select *
from t 
where op > 1;

Which one you prefer is largely a matter of taste. CTEs are optimized in the same way as derived tables are, so the first one might be faster especially if there is an index on the expression cos(id) + cos(id)

Use a calculated column in a where clause

Using Derived Columns in a predicate

You'll need to wrap the inner query in a derived table or CTE in order to be able to use derived columns in the WHERE clause (Also, note SUM() is specified just once, using the results of the multiplication):

SELECT x.Code, x.AccountNumber, x.Sales
FROM
(
  SELECT p.Code, c.AccountNumber, SUM(p.UnitPrice *od.QtyShipped) AS Sales 
  FROM [dbo].Customer c 
      LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id 
      LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
      LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
  GROUP BY p.Code, c.AccountNumber
) AS x
WHERE x.Sales > 100;

Repeating the Derived Column in a HAVING clause

As per @Jonny's comment, the other way is not to DRY up the calculated column, but to instead repeat the calculation. Use HAVING instead of WHERE after a GROUP BY has been applied.

SELECT p.Code, c.AccountNumber, SUM(p.UnitPrice *od.QtyShipped) AS Sales 
FROM [dbo].Customer c 
  LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id 
  LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
  LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
GROUP BY p.Code, c.AccountNumber
HAVING SUM(p.UnitPrice * od.QtyShipped) > 100;

In either case, as per comments below, note that the calculated expression is SUM(p.UnitPrice * od.QtyShipped) and not SUM(p.UnitPrice) * SUM(od.QtyShipped).

PostgreSQL Calculated Column with values of another table referenced by foreign key

You cannot define a generated column based on values from other tables. Per the documentation:

The generation expression can refer to other columns in the table, but not other generated columns. Any functions and operators used must be immutable. References to other tables are not allowed.

You can achieve the expected behavior by creating two triggers on both tables but usually creating a view based on the tables is a simpler and more efficient solution.

---

Related Topics