Reference column alias in same SELECT list
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;
- https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#LATERAL_JOIN
- How to retrieve closest value based on look-up table?
I added NULLIF()
to defend against division-by-zero errors.
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 ;