Question 89539 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL EXISTS and null value

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?

Tags:

2

Answers


  1. 0

    exists only tests the presence of a row, not the value(s) returned by the query.

    In fact, the expression in the select list isn’t not even evaluated for an EXISTS operator.

    You could even write select exists (select 1/0) without getting an error (this would return true). Although select 1/0 as a standalone query would result in an error.

    Login or Signup to reply.
  2. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search