"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.

Answer 1

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!

Answer 2

You can only reference input column names in a SELECT list, not output column names (aliases). 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?

Answer 3

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';