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

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.

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?

StackExchange Database Administrators Q#322239, answer score: 4