How to Correctly Perform Calculations Using Calculated Columns in PostgreSQL Queries?

Correctly use calculated columns in PostgreSQL queries for calculations

When processing data in PostgreSQL, you often need to perform calculations on columns to derive new information. For this reason, PostgreSQL provides the functionality to create computed columns that can be used as part of the same query that creates them.

Issue: Incorrect SQL syntax

Consider the following SQL statement, which works in other database management systems (DBMS) but fails in PostgreSQL:

<code class="language-sql">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;</code>

PostgreSQL will throw an error stating that columns total_1 and total_2 do not exist.

Solution: Wrap the query in a derived table

To solve this problem, PostgreSQL requires that the SELECT statement be wrapped in a derived table:

<code class="language-sql">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;</code>

This method allows you to access column aliases in the derived table, including computed columns total_1 and total_2. It does not cause any performance penalty.

Notes

It is worth noting that raw SQL statements that allow calculated columns to be used directly in subsequent calculations are not recommended in PostgreSQL or other DBMSs. It can cause performance issues and hinder query optimization.

The above is the detailed content of How to Correctly Perform Calculations Using Calculated Columns in PostgreSQL Queries?. For more information, please follow other related articles on the PHP Chinese website!