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.

closed as off-topic by dezso, hot2use, RDFozz, LowlyDBA, MDCCL Mar 12 '18 at 17:04

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, hot2use, RDFozz, LowlyDBA, MDCCL
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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

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