Reference column alias in same SELECT list

Question

I'm converting an old MS-Access-based system to PostgreSQL. In Access, fields that were made up in SELECTs could be used as parts of equations for later fields, like this:

SELECT
    samples.id,
    samples.wet_weight / samples.dry_weight - 1 AS percent_water,
    100 * percent_water AS percent_water_100
FROM samples;

When I do this in PostgreSQL, Postgres throws an error:

ERROR: column "percent_water" does not exist.

Here's how I can work around it, by selecting out of a sub-selection:

SELECT
    s1.id,
    s1.percent_water,
    100 * s1.percent_water AS percent_water_100
FROM (
    SELECT
        samples.id,
        samples.wet_weight / samples.dry_weight - 1 AS percent_water
    FROM samples
    ) s1;

Is there any kind of shortcut like in the first code block to get around complicated nesting? I could also just say 100 * (samples.wet_weight / samples.dry_weight - 1) AS percent_water_100, but this is just a small example out of what is a much larger system of math going on in my code, with dozens of more complex bits of math stacked on top of each other. I'd prefer to do as cleanly as possible without repeating myself.

Erwin Brandstetter · Accepted Answer · 2022-06-14T21:32:15.467

It's inconvenient sometimes, but it's SQL standard behavior, and it prevents ambiguities. Expressions in the SELECT list cannot reference output column names from the same SELECT list, only input column names from relations in the FROM clause.

There are shorter syntax options:

SELECT s.*, s.percent_water * 100 AS percent_water_100
FROM  (
   SELECT id, wet_weight / NULLIF(dry_weight - 1, 0) AS percent_water
   FROM   samples
   ) s;

And you can use a LATERAL join in Postgres 9.3+:

SELECT s.id, s1.percent_water
     , s1.percent_water * 100 AS percent_water_100
FROM   samples s
     , LATERAL (SELECT s.wet_weight / NULLIF(s.dry_weight - 1, 0) AS percent_water) s1;

NULLIF() defends against division-by-zero errors.

score 7 · Answer 2 · edited Nov 01 '16 at 22:16

I hit something like this migrating a 500+ line Netezza query (aka modified Postgres) to SQL Server. In Netezza the computed column alias was allowed to be used as a value in downstream references.

My work around was to use CROSS APPLY with a correlated sub-query. The beauty of it is that the numerous references to the column alias in original query did not need to be changed at all.

Using the query from the OP, the CROSS APPLY method would look something like:

SELECT
    s.id,
    x.percent_water,
    100 * x.percent_water AS percent_water_100
FROM samples AS s
CROSS APPLY (SELECT s.wet_weight / s.dry_weight - 1 AS percent_water ) x ;

Reference column alias in same SELECT list

2 Answers2

Linked