Return 1 if number exists in table and 0 otherwise

Question

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.

Answer 1

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:

• Return a value if no record is found

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:

• Are results from UNION ALL clauses always appended in order?

Answer 2

If the field you are testing is the Primary Key (or some other unique constraint), then you can simply return the count (which will always be 0 or 1):

SELECT count(*) FROM test WHERE id = 100;

Answer 3

it easy and quick:

SELECT ISNULL((SELECT 1 FROM test WHERE id = 100), 0) res