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.

Answer

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 is 1.
FALSE is 0.

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.

See:

• Return a value if no record is found