checking for boolean true / false result in postgresql query

Question

I am running the following sql query in my web app:

SELECT EXISTS (
SELECT id
FROM user
WHERE membership=1244)

i was expecting true (boolean data) as the result but I'm getting 't' or 'f' for false. How do I get it to return to my lua code a standard boolean?

I found the following post:

Reading boolean correctly from Postgres by PHP

And so I tried to change my code to something like this:

SELECT EXISTS ::int (
SELECT id
FROM user
WHERE membership=1244)

or

SELECT ::INT (SELECT EXISTS (
SELECT id
FROM user
WHERE membership=1244))

But I'm getting a syntax error.
Can you tell the best way to handle this? Should I be casting the resulting 't' to a boolean somehow? or is there a way to tell postgresql to return true / false instead of 't'/'f'?

Thanks.

Answer 1

You are so close

SELECT EXISTS (SELECT id FROM user WHERE membership=1244)::int 

Answer 2

Try it with CASE

select       
  (case when exists (SELECT id FROM user WHERE membership = 1244)
    then 1 
    else 0 
  end) as column;

my test fiddle

Answer 3

Your first query do indeed return a boolean. A t is shown as the returned value of the query

select exists (select 1);
 exists 
--------
 t

But if you check its type it is a boolean:

select pg_typeof(exists (select 1));
 pg_typeof 
-----------
 boolean

You will have to check with the lua's postgresql driver manual how to properly handle it.

Answer 4

Your initial query is fine by itself:

SELECT id FROM user WHERE membership=1244

You just need to check if it returns a row or not.