Cast all boolean fields in a SELECT * query to integer

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.

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 which might do it.

PostgreSQL doesn't have a display type for output. 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

StackExchange Database Administrators Q#165742, answer score: 2