3

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.

1
  • I’ve run into something similar earlier. You can use CTEs, even nested, and then refer to their output column names by referencing the CTE in the FROM list of your query. Nesting example: WITH foo AS (SELECT a, b, a+b AS c FROM tbl), bar AS (SELECT a, b, c, c::BIGINT AS d, c - c::BIGINT AS e) SELECT d, e FROM bar;
    – mirabilos
    Jan 19, 2023 at 1:55

3 Answers 3

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!

3
  • Worked, thanks @Peter
    – Aamir
    Jan 18, 2023 at 7:17
  • How can some syntactic nonsense with barely an explanation accumulate many upvotes, I wonder? Jan 18, 2023 at 8:52
  • 3
    Good question, I must say, I prefer your answer!
    – Peter
    Jan 18, 2023 at 8:55
4

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

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:

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:

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.