Search code examples
sqlpostgresqlbooleanexists

Return 1 if number exists in table and 0 otherwise

I want to return 1 if some number already exists in table and 0 otherwise.

I tried something but it doesn't work:

select
case when 100 in (select distinct id from test) then '1'
else '0'
from test

I want something similar to exists function that already exists in PostgreSQL, but instead of true and false I want 1 or 0.

Solution

  • EXISTS yields a boolean result.
    The simple way to achieve what you are asking for is to cast the result to integer:

    SELECT (EXISTS (SELECT FROM test WHERE id = 100))::int;

    TRUE becomes 1.
    FALSE becomes 0.

    See:

    Or with UNION ALL / LIMIT 1 (probably slightly faster):

    SELECT 1 FROM test WHERE id = 100
UNION  ALL
SELECT 0
LIMIT  1;

    If a row is found, 1 is returned and Postgres stops execution due to LIMIT 1. Else, 0 is returned.

    Disclaimer for the UNION ALL solution: this relies on undocumented behavior that Postgres would execute UNION ALL terms in sequence. This used to be the case until Postgres 11, where Parallel Append was added to Postgres. It's actually still the case (currently Postgres 15) for the given example, as retrieving a single row will never trigger a Parallel Append plan. But it remains undocumented behavior, so you might not want to rely on it. See: