Reference column alias in same SELECT list

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.

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;

• https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#LATERAL_JOIN

• How to retrieve closest value based on look-up table?

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

StackExchange Database Administrators Q#96556, answer score: 49