63

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.

1
  • I’ve run into something similar earlier. You can use CTEs, even nested, and then refer to their output column names by referencing the CTE in the FROM list of your query. Nesting example: WITH foo AS (SELECT a, b, a+b AS c FROM tbl), bar AS (SELECT a, b, c, c::BIGINT AS d, c - c::BIGINT AS e) SELECT d, e FROM bar;
    – mirabilos
    Commented Jan 19, 2023 at 1:55

2 Answers 2

49

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.

2
  • 7
    Hi. May you extend your answer by an example which ambiguities SQL standart prevents? Commented Mar 7, 2019 at 8:26
  • 1
    @Eugen: Ambiguities like the following: SELECT a*2 AS b, b*2 AS c FROM tbl; And then somebody add a column b to the table ... Commented Apr 29, 2023 at 5:43
7

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 ;
2
  • 2
    CROSS APPLY (and OUTER APPLY) is the SQL Server way of writing LATERAL subqueries. Commented Nov 1, 2016 at 22:17
  • 6
    There is no cross apply in Postgres. Postgres sticks to the standard and uses cross join lateral.
    – user1822
    Commented Nov 1, 2016 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.