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".

  • Can you give an example of a value that you are trying to prevent? Post create table statement – Lennart Nov 13 '19 at 5:38
  • Are you looking for a boolean expression (rather than a CASE expression)? e.g. value > 42 – a_horse_with_no_name Nov 13 '19 at 6:40
  • 1
    Could you provide more detail about what you mean by "set this up?" – richyen Nov 13 '19 at 7:28
  • Show an example of a CASE statement that is not clean enough for you. – CL. Nov 13 '19 at 9:22
  • @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. – Luis Arriojas Nov 13 '19 at 12:44

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

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

  • 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". – Luis Arriojas Nov 13 '19 at 12:36
  • The t output completely depends on the SQL client you are using – a_horse_with_no_name Nov 13 '19 at 12:36
  • @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. – Luis Arriojas Nov 13 '19 at 12:41
  • 2
    @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 – a_horse_with_no_name Nov 13 '19 at 12:43
  • 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 – eagle275 Nov 13 '19 at 12:45

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?


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)) $$

select showbool(b) from T
  • 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. – Luis Arriojas Nov 13 '19 at 12:38
  • I would say that 'no' etc are casted to boolean – Lennart Nov 13 '19 at 13:14

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.

  • 1
    To "close" this question, you need to accept an answer. It's perfectly fine to accept your own answer. – a_horse_with_no_name Nov 15 '19 at 6:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.