Postgresql – Using column alias in a WHERE clause doesn’t work

aliaspostgresqlsubquerywhere

Given a table users with two fields: id and email.

select id, email as electronic_mail 
from (  
        select id, email 
        from users
) t 
where electronic_mail = ''

Postgres complains that:

ERROR:  column "electronic_mail" does not exist

The example is just to demonstrate the arising problem. My actual case is more complex, I iterate though an array of elements in a json column, taking a single scalar value from each. (I can share some code if that helps.)

I really don't get what would be the complication, probably I'm unaware of something. I was under the impression that aliased columns can be employed in a WHERE clause without problem?

Best Answer

The manual clarifies here:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

That's according to the SQL standard and may not be very intuitive. The (historic) reason behind this is the sequence of events in a SELECT query. WHERE and HAVING are resolved before column aliases are considered, while GROUP BY and ORDER BY happen later, after column aliases have been applied.
Also note that conflicts between input and output names are resolved differently in ORDER BY and GROUP BY - another historic oddity (with a reason behind it, but potentially confusing nonetheless). See:

PostgreSQL: How to return rows with respect to a found row (relative results)?

Best to avoid column aliases that conflict with input column names a priori.

Aside: the subquery in your example is just noise since the WHERE clause is part of the outer query, so the example can be simplified to:

select id, email as electronic_mail 
from users t 
where electronic_mail = '';  -- doesn't work

Related Solutions

Use an Alias in Where Clause Subquery

@haki has identified why this error occurs. You can't use an alias (KODE) defined in the SELECT list, in the definition of another column in that list. You have to use the full reference instead (ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI).

But the solution provided, does not work I think because of the double nesting. You can reference a column in a inline subquery but not in a derived table inside an inline subquery.

This would require either using ROW_NUMBER() and encapsulating the whole query ina derived table or turning the inline subquery into a LEFT JOIN. But since you have ROWNUM=1 and ORDER BY TMT_JABATAN desc and you are selecting only TMT_JABATAN in that inline subquery, you can use MAX(TMT_JABATAN) instead and one-level nesting:

SELECT
    sp.ID_PEGAWAI AS KODE,
    sp.NAMA,
    sp.NIP,
    sej.JABATAN,
    skgp.GOLONGAN,
    skgp.PANGKAT,
    ( SELECT CONCAT(TO_CHAR(abs(sysdate - TO_DATE(MAX(sj.TMT_JABATAN)))/360,'9,999,999.9'),
                    ' TAHUN') 
      FROM SIMPEG_JABATAN  sj
      WHERE sj.ID_PEGAWAI = sp.ID_PEGAWAI  
    ) AS MASA_KERJA
FROM 
        ANGGARAN.SIMPEG_PEGAWAI  sp
    INNER JOIN ANGGARAN.SIMPEG_ESELON_JABATAN   sej
        ON sp.ESELON_JABATAN = sej.ID_ESELON_JABATAN
    INNER JOIN ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT  skgp
        ON sp.PANGKAT = skgp.ID_GOLONGAN_PANGKAT
WHERE
    sp.ST_AKTIF = 1 AND
    sp.ESELON2 <> 1 AND
    sp.PANGKAT >= 12 AND 
    sej.STATUS = 1 AND
    sej.ID_ESELON2 = 2
ORDER BY
    skgp.SORT DESC ;

Postgresql – Create smaller row from alias and preserve column names

One idea is to create an inline view containing the columns you are interested in:

SELECT users."name" AS "name"
     , array_to_json(array_agg(sites)) as sites
FROM remodel.users AS users
JOIN remodel.user_sites AS user_sites
    ON users.id=user_sites.user
CROSS JOIN LATERAL (
    SELECT name, created 
    FROM remodel.sites AS s
    WHERE s.id=user_sites.site
) AS sites
GROUP BY "users".id

By using LATERAL you can reference the "sibling" in the inline view. Since the JOIN condition is part of the view a CROSS JOIN can be used.

Best Answer

Related Solutions

Use an Alias in Where Clause Subquery

Postgresql – Create smaller row from alias and preserve column names

Related Question