CODEWITHSUNDEEP
sqlpostgresqlboolean
Serge Vinogradoff
Serge Vinogradoff

Reputation: 2272

Output "yes/no" instead of "t/f" for boolean data type in PostgreSQL

How do I make a query that would return yes/no instead of t/f (true/false)?

Current solution is: 

SELECT credit_card_holders.token IS NOT NULL AS premium

I found a Ruby solution: Rails (or Ruby): Yes/No instead of True/False

But would rather do it in PostgreSQL if possible.

Upvotes: 10

Views: 13719

Answers (4)

mkataja
mkataja

Reputation: 990

The most straightforward way in case true/false is good enough:

SELECT (credit_card_holders.token IS NOT NULL)::text AS premium

At least it's more readable than t/f!

Upvotes: 3

Vivek S.
Vivek S.

Reputation: 21905

by creating custom types also you can achieve this, see the following example

create table foo (id int,xid int);
insert into foo values (1,2),(2,3);

we have following data

id xid 
-- --- 
1  2   
2  3

and the following select statements returns boolean value.

select exists(select * from foo where xid=4);

exists
boolean
------
f

select exists(select * from foo where xid=3);

exists
boolean
------
t

ok now we need to return YES and NO instead of t and f, so we can create a custom type like below

create type bool2yesno as enum ('YES','NO'); --or whatever you want 'yes','no'.

and create a function to convert boolean to the created custom type i.e bool2yesno

create function convert_bool_to_bool2yesno(boolean)
  returns bool2yesno
  immutable
  strict
  language sql
as $func$
  select case $1
    when false then 'NO'::bool2yesno
    when true  then 'YES'::bool2yesno
  end
$$;

now create a cast for the newly created type

create cast (boolean as bool2yesno )
  with function convert_bool_to_bool2yesno(boolean)
  as assignment;

now again try the select statement

select exists(select * from foo where xid=4)::bool2yesno ;

exists 
bool2yesno 
----------
NO     

select exists(select * from foo where xid=3)::bool2yesno ; 
exists 
bool2yesno 
---------- 
YES

Reference :
CREATE TYPE
CREATE CAST
CREATE FUNCTION

Upvotes: 7

Serge Vinogradoff
Serge Vinogradoff

Reputation: 2272

Ended up with this:

(case when credit_card_holders.token IS NOT NULL then 'Yes' else 'No' end) AS premium

Upvotes: 12

Andrey Demidenko
Andrey Demidenko

Reputation: 200

With this gem humanize_boolean you can do so

true.humanize # => "Yes" false.humanize # => "No"

Upvotes: -1

Related Questions