Why 't' and 'f' instead of TRUE and FALSE

Question

Why does PostgreSQL return 't' and 'f' instead of TRUE and FALSE?

The documentation itself advises us to use TRUE and FALSE when inserting boolean values or making comparisons, but returns a different set of values when selecting the value from the database.

CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, 'sic est');
INSERT INTO test1 VALUES (FALSE, 'non est');
SELECT * FROM test1;
 a |    b
---+---------
 t | sic est
 f | non est

Is there a reason for this behavior? Have the PostgreSQL commmunity ever tried to change that?

Answer 1

PostgreSQL does not return these instead of boolean values. It is some clients (for example, psql and pgAdminIII) which represents TRUE with t and FALSE with f - try the same query in another client and you will see something else. See, for example, what DBVisualizer gives you:

I guess the reason for showing t and f is simply sparing space in a command-line client that lacks horizontal scrolling abilities.

Note: I am in no way affiliated to DbVis Software.

Answer 2

_{Community wiki answer:}

TRUE and FALSE as cited in the question context are keywords, not values that can be returned to a client. If true as a value was returned that would be 4 times bigger than t, so that's not better.

Statistically 1 and 0 are often present as numbers so they would be harder to distinguish. OTOH t or f is a strong visual hint that we have a Boolean. Same as Y and N in a char column.

boolean is neither binary nor a bitstring. boolean is boolean and operates with a ternary logic around the values true, false and null.

Some other RDBMS lack a proper implementation, which may add to the confusion. If you want 0 and 1 instead, just cast the boolean to integer e.g. SELECT true::int.

Related update in Postgres 9.5:

• Use assignment cast behavior for data type conversions in PL/pgSQL assignments, rather than converting to and from text (Tom Lane)

This change causes conversions of Booleans to strings to produce true or false, not t or f. Other type conversions may succeed in more cases than before; for example, assigning a numeric value 3.9 to an integer variable will now assign 4 rather than failing. If no assignment-grade cast is defined for the particular source and destination types, PL/pgSQL will fall back to its old I/O conversion behavior.