Return Boolean Value as TRUE or FALSE in Select (PostgreSQL/pgAdmin)

26
votes

In PostgreSQL (version 9.4, pgAdmin3), when doing select on a table with boolean column the data output shows 't' or 'f'. I would like to cast/convert booleans as TRUE or FALSE without writing CASE statements or doing JOINS etc.

BTW, according to PostgreSQL own documentation this behavior is not the SQL standard.

The key words TRUE and FALSE are the preferred (SQL-compliant) usage.

PS: This happens only when using the SQL Editor in pgAdmin. Use pgAdmin object browser, drill down to same table, right-click, view data, View Top 100 rows, the same boolean column shows up as TRUE or FALSE, as expected/standard.

2
sqlpostgresqlboolean
Whether it displays as "t"/"f" or "TRUE"/"FALSE" is simply a display issue. The boolean is returning the value of TRUE or FALSE (note, no quotes). How that gets represented on the display is up to that software. I would strongly suggest against trying to return a string value to get around a display issue here. – Tom H
Correct - just a cosmetic issue. I added pgAdmin to title of my question to clarify. Also the display is specifically in pgAdmin SQL Editor. So I guess it just a enhancement request in next release of pgAdmin - to be consistent with their own documentation and SQL standard ;-) – Net Dawg

2 Answers

28
votes

If all you want to show is the literal TRUE or FALSE, you can use the case statements like you had proposed. Since PostgreSQL treats TRUE, true, yes, on, y, t and 1 as true, I'd control how I'd want the output to look like.

Where clause can be written like:

select * from tablename where active
--or--
select * from tablename where active = true

(My recommendation is the same as PostgreSQL - use true)

When selecting, although there may be hesitation to use the case statements, I'd still recommend doing that to have control over your output string literal.

Your query would look like this:

select 
  case when active = TRUE then 'TRUE' else 'FALSE' end as active_status,
  ...other columns...
from tablename
where active = TRUE;

SQLFiddle example: http://sqlfiddle.com/#!15/4764d/1

create table test (id int, fullname varchar(100), active boolean);
insert into test values (1, 'test1', FALSE), (2, 'test2', TRUE), (3, 'test3', TRUE);

select
  id,
  fullname,
  case when active = TRUE then 'TRUE' else 'FALSE' end as active_status
from test;

| id | fullname | active_status |
|----|----------|---------------|
|  1 |    test1 |         FALSE |
|  2 |    test2 |          TRUE |
|  3 |    test3 |          TRUE |
18
votes

A simple cast to text will do the job (unless you need upper case spelling):

SELECT true::text AS t, false::text AS f;

 t    | f
------+-------
 true | false

Else, the text representation depends on library and client you use to connect. JDBC for instance renders boolean values as 'true' / 'false' anyway:

db<>fiddle here
Old sqlfiddle

You will love this change in Postgres 9.5 (quoting the release notes):

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

Bold emphasis mine.