How to change the default output format for boolean values

Question

In a Postgres database, I know how to use case statements to get only TRUE or FALSE for a boolean field but was wondering whether there is a cleaner way to set this up like changing any configuration in the db.

Update: in Postgres, a the boolean representation for true is not only TRUE but also: yes, y, on, "t". So, I am inserting values with value TRUE. However, the SELECT command returns those values as "t".

• Lennart - Slava Ukraini: Can you give an example of a value that you are trying to prevent? Post create table statement
• user1822: Are you looking for a boolean expression (rather than a CASE expression)? e.g. value > 42
• richyen: Could you provide more detail about what you mean by "set this up?"
• CL.: Show an example of a CASE statement that is not clean enough for you.
• Luis Arriojas: @Lennart I created my table using "CREATE TABLE T ( b boolean not null default FALSE );". Inserted a TRUE value but the Select command returns "t". So, when I send this to Angular app, it expects to receive a boolean value, not a string.

Answer 1

I have found the root of this issue. Postgres is fine. It returns boolean as true and numbers as int except decimal type values.

The problem was I was using pg_connect in PHP and fetchAll of this library casts everything to string. I am using PDO now and everything is normal.

Lets close this thread.

• user1822: To "close" this question, you need to accept an answer. It's perfectly fine to accept your own answer.

Answer 2

if I understand you .. use this https://www.postgresql.org/docs/9.3/ddl-default.html

CREATE TABLE T (
b boolean not null default FALSE
);

on the other hand postgres still allows for more values that will be translated to true or false (Table of literal values for true and false according to http://www.postgresqltutorial.com/postgresql-boolean/)

True False
true false
‘t’ ‘f‘
‘true’ ‘false’
‘y’ ‘n’
‘yes’ ‘no’
‘1’ ‘0’

so you probably can't enforce true / false - on other hand a select will deliver true / false as you want

• Luis Arriojas: I have updated my question but yes, that's what I want to avoid. If I insert TRUE, I don't expect to get "t".
• user1822: The t output completely depends on the SQL client you are using
• Luis Arriojas: @eagle275 I created my table using the same command you posted. Inserted a TRUE value but the Select command returns "t". So, when I send this to Angular app, it expects to receive a boolean value, not a string.
• user1822: @LuisArriojas: Postgres does return real boolean value. The t you are seeing is just the default output format applied by e.g. psql. If you retrieve the value (correctly) in a programming language, you will get a proper boolean value. Don't confuse output formatting with the actual column value
• eagle275: That's what I accounted for - given its some years back that I supported a team mate with postgre but I remember that the boolean coliumn beautifully corresponded to a boolean value in my chosen programming language (JAVA at that time) - but Angular as a webservice might blur the lines

Answer 3

There is probably something that I don't understand about your question but given a table with a boolean attribute like:

create table T ( b boolean not null );

You can only insert True, False. Under what circumstances would you need a CASE expression?

Edit:

reading your comments above, I guess you are interested in a textual representation of t/f. Not sure if it fits your needs but you can cast it to text:

select b::text from T

If you want it to be TRUE/FALSE I guess you will have to:

select upper(b::text) from T

Perhaps a function would be cleaner?

CREATE FUNCTION showbool(boolean) RETURNS text
 AS $$ SELECT UPPER(CAST(1ドル AS text)) $$
 LANGUAGE SQL;
select showbool(b) from T

• Luis Arriojas: Boolean in postgres is much more than only true and false. You can use": True False, true, false, ‘t’, ‘f‘, ‘true’, ‘false’, 'y’, ‘n’, ‘yes’, ‘no’, ‘1’, ‘0’, on, off.
• Lennart - Slava Ukraini: I would say that 'no' etc are casted to boolean