PostgreSQL EXISTS and null value

Question

For example use Test table with only positive id value. Query select 1 from public.Test where id = 0 return empty result.

But when I use this query:

select case 
           when exists(select 1 from public.Test where id = 0) then 'exist' 
           else 'not exist' 
       end

result is - not exist

For query:

select case when exists(select null) then 'exist' else 'not exist' end

result is - 'exist'

And for query:

do $$
declare _query int = 7;
begin
_query:= (select 1 from public.Test where id = 0);
RAISE NOTICE '%', _query;
end;
$$;

result is - NULL

So what's the difference between query returning empty result and null? And why does the _query variable for the empty query result have a null value?

Answer 1

You just want:

SELECT EXISTS(SELECT FROM public.test WHERE id = 0) AS any_matching_rows

No extra CASE wrapper. EXISTS returns true / false (never null). All done.
The SELECT list of the nested query can stay empty as it's irrelevant to EXISTS.

And why does the _query variable for the empty query result have a null value?

That's because "no row" is converted to a null value in an assignment to a scalar variable. (Feeding that query to EXISTS converts "no row" to false instead - and the existence of any rows to true.)