How Can I Use Calculated Columns in the Same PostgreSQL Query?

PostgreSQL Calculated Columns: A Practical Guide

Working with calculated columns within a single PostgreSQL query can sometimes present challenges. Unlike some other SQL databases, PostgreSQL's handling of calculated columns requires a specific approach.

Addressing SQL Syntax Incompatibilities

The following SQL code, functional in many other database systems, will fail 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,
       (total_1 + total_2) AS total_3
FROM data;</code>

PostgreSQL will report that total_1 and total_2 are undefined. This is because PostgreSQL treats calculated columns as aliases, ephemeral to the query's execution.

Effective Solution: Leveraging Derived Tables

The solution involves using a derived table (or subquery) to overcome this limitation:

<code class="language-sql">SELECT cost_1, 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
) AS t;</code>

By nesting the initial SELECT statement within a derived table (aliased as t), the outer query can then successfully reference the total_1 and total_2 aliases. This method introduces no performance overhead.

The above is the detailed content of How Can I Use Calculated Columns in the Same PostgreSQL Query?. For more information, please follow other related articles on the PHP Chinese website!