Return 1 if a number exists in the table and 0 otherwise

Question

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

I tried something, but it does not work:

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

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

Answer 1

EXISTS gives the result boolean .
An easy way to achieve what you are asking for is to pass the result to an integer :

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

TRUE - 1 .
FALSE 0 .

Answer 2

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

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