0

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

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

3 Answers 3

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.

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

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

5
  • 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". Commented Nov 13, 2019 at 12:36
  • The t output completely depends on the SQL client you are using
    – user1822
    Commented Nov 13, 2019 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. Commented Nov 13, 2019 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
    – user1822
    Commented Nov 13, 2019 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
    Commented Nov 13, 2019 at 12:45
0

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
2
  • 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. Commented Nov 13, 2019 at 12:38
  • I would say that 'no' etc are casted to boolean Commented Nov 13, 2019 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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