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

Question

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.

Answer 1

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 |