"ERROR: column "a" does not exist" when referencing column alias

Question

I am attempting to retrieve either a null or boolean value from a query. Here is my query:

SELECT EXISTS (SELECT 1 FROM employee
               where add_uuid = '7e53fa47-ade3-4e94-8efd-b25f998b09c6') AS a,
       CASE WHEN a = false then null
            ELSE (SELECT exists (SELECT 1 FROM employee
                                 where add_uuid = '7e53fa47-ade3-4e94-8efd-b25f998b09c6'
                                 and is_active = true
                                )
                 )
       END

Results in:

SQL Error [42703]: ERROR: column "a" does not exist

Despite experimenting with multiple options, I am still unable to achieve the desired outcome.

Erwin Brandstetter · Answer 1 · 2024-11-21T17:08:28.907

You can only reference input column names in a SELECT list, not output column names (column aliases from the same query level). See:

Reference column alias in same SELECT list

If add_uuid is defined UNIQUE, and is_active defined NOT NULL, it all burns down to just:

SELECT true AS a, is_active AS b
FROM   employee
WHERE  add_uuid = '7e53fa47-ade3-4e94-8efd-b25f998b09c6';

This avoids scanning the table repeatedly, and also avoids evaluating a (possibly expensive) count(). It only retrieves a single row - ideally supported by an appropriate index on (add_uuid).

If we can make no assumptions about your undisclosed table definition:

SELECT true AS a
     , COALESCE(is_active, false) AS b
FROM   employee
WHERE  add_uuid = '7e53fa47-ade3-4e94-8efd-b25f998b09c6'
ORDER  BY NOT is_active
LIMIT  1;

ORDER BY NOT is_active sorts a row with true first. About ordering by boolean:

Best way to check for "empty or null value"

COALESCE() is only there to convert a possible null value to false.

In case there are no qualifying rows, the result is "no row", which should be clear enough. If you want (false, null) explicitly for this:

(
SELECT true AS a
     , COALESCE(is_active, false) AS b
FROM   employee
WHERE  add_uuid = '7e53fa47-ade3-4e94-8efd-b25f998b09c6'
ORDER  BY NOT is_active
LIMIT  1
)
UNION ALL
SELECT false, null
ORDER  BY a DESC
LIMIT  1;

The outer ORDER BY is typically not needed, but to make sure. See:

Are results from UNION ALL clauses always appended in order?

Peter · Accepted Answer · 2023-01-18T09:00:09.670

3

Aliases, like A, are not accessible in other columns due to the processing of the query.

You should make it:

CASE WHEN SELECT 1 FROM employe where add_uuid = '7e53fa47-ade3-4e94-8efd-b25f998b09c6' = false THEN NULL etc..

Edit Peter: See Erwin's answer for a better answer!

edited Jan 18 '23 at 09:00

answered Jan 18 '23 at 06:58

Peter

2,530
2
6
20

score 2 · Answer 3 · answered Jan 18 '23 at 07:54

You cannot use the alias for one element of the SELECT list in another element. That would imply that those elements are processed in a certain order, which they aren't.

You could simplify your code with a subquery:

SELECT 0 < count(*) AS a,
       CASE WHEN 0 < count(*)
            THEN 0 < count(*) FILTER (WHERE is_active)
       END
FROM employee
WHERE add_uuid = '7e53fa47-ade3-4e94-8efd-b25f998b09c6';

"ERROR: column "a" does not exist" when referencing column alias

3 Answers3