27

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.

24

It's inconvenient sometimes, but it's SQL standard behavior, and it prevents ambiguities. You cannot reference column aliases in the same SELECT list.

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;

I added NULLIF() to defend against division-by-zero errors.

  • 2
    Hi. May you extend your answer by an example which ambiguities SQL standart prevents? – Eugen Konkov Mar 7 at 8:26
4

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 ;
  • 1
    CROSS APPLY (and OUTER APPLY) is the SQL Server way of writing LATERAL subqueries. – ypercubeᵀᴹ Nov 1 '16 at 22:17
  • 4
    There is no cross apply in Postgres. Postgres sticks to the standard and uses cross join lateral. – a_horse_with_no_name Nov 1 '16 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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