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.