In PostgreSQL, I can cast individual boolean fields to integer when I want to get 0/1 output instead of f/t.

SELECT some, thing, mybool::integer FROM mytable;

But is there a way to ask PostgreSQL to do it for any boolean field in the query, without specifying the field name?

If I have

SELECT * FROM mytable;

I would like any bools to be output as 0/1.

  • 3
    I thinks it is not about postgres, it is more about the app you use to deal with it. Are you using pgAdmin? – McNets Feb 28 '17 at 15:38
  • In this particular case, I'm using psql. Didn't see anything in it's options or pset settings, so I hoped there might be some global config in the database, or some obscure SQL syntax wich might do it. – mivk Feb 28 '17 at 23:07
  • Actually postgres accepts this values for input: postgresql.org/docs/9.5/static/datatype-boolean.html – McNets Feb 28 '17 at 23:10
  • Yes, it works on input, but I want to specify the format for outputs. – mivk Mar 1 '17 at 13:01

PostgreSQL doesn't have a display type for output, so the question is just somewhat kind of silly. Using libpq you have two options

  • Binary
  • Text

You don't get to customize how it delivers text back. And, it wouldn't make much sense either way. The library exists for people to build interfaces with, not for end users. Using C, if you're taking 1-byte for a boolean value, it's better to have t and f because 0 (the integer) and '0' the string are fundamentally different but share the same gylph "0" and description "zero". Now for psql I think you may have a valid feature request for that. Perhaps in the future you'll see something like

\pset numericbinary

And you're welcome to open a feature request on that.

You can see this hard coded in the source here

| improve this answer | |

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