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.

Birei · Accepted Answer · 2019-03-22 14:52:27Z

28

You are so close

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

edited Mar 22, 2019 at 14:52

Birei

36.4k3 gold badges80 silver badges84 bronze badges

answered Jul 23, 2013 at 15:28

Barbara Laird

12.7k2 gold badges48 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Barbara Laird Over a year ago

In Postgres 9.1.9, it works fine. See the sqlfiddle.com/#!1/bfe1e/2 Also, a 0 or 1 for PHP works as boolean

Barbara Laird Over a year ago

No problem. You gave me an excuse to use sqlfiddle. Which I just learned about today, so I'm excited. :)

Clodoaldo Neto · Accepted Answer · 2013-07-23 16:00:59Z

8

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.

answered Jul 23, 2013 at 16:00

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Comments

Michael Gaskill · Accepted Answer · 2016-06-06 08:53:28Z

5

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

edited Jun 6, 2016 at 8:53

Michael Gaskill

8,06210 gold badges40 silver badges46 bronze badges

answered Jul 23, 2013 at 14:30

slavoo

6,11464 gold badges40 silver badges41 bronze badges

Comments

Milen A. Radev · Accepted Answer · 2013-07-23 17:40:11Z

0

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.

answered Jul 23, 2013 at 17:40

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Collectives™ on Stack Overflow

checking for boolean true / false result in postgresql query

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related