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.
Ended up with this:
(case when credit_card_holders.token IS NOT NULL then 'Yes' else 'No' end) AS premium
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
no for NULL it is need to remove strict attribute in convert_bool_to_bool2yesno function definition.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!
With this gem humanize_boolean you can do so
true.humanize # => "Yes"
false.humanize # => "No"
