Converting Boolean Values to TRUE or FALSE in PostgreSQL Select Queries

Overview of Boolean Display Behavior in PostgreSQL

In the PostgreSQL database management system, boolean data types exhibit a notable characteristic difference in their display behavior. When executing queries using pgAdmin's SQL editor, boolean columns default to displaying as single characters 't' (for true) or 'f' (for false). However, according to PostgreSQL's official documentation, this representation does not conform to SQL standards, which recommend using the keywords TRUE and FALSE.

Interestingly, this display discrepancy only occurs in the SQL editor. If navigating to the same table through pgAdmin's object browser and selecting "View Data" or "View Top 100 Rows," the same boolean column correctly displays as TRUE or FALSE. This inconsistency can cause confusion for developers, particularly when generating standardized reports or integrating with other systems.

Solution Using CASE Statements

The most direct and controllable solution involves using CASE statements to explicitly convert boolean values to the desired display format. While this approach requires additional coding effort, it provides complete control over the output format, ensuring it meets specific requirements.

PostgreSQL accepts a wide range of representations for boolean true values, including TRUE, true, yes, on, y, t, and 1. In WHERE clauses, boolean column names can be used directly as conditions without explicit comparison:

SELECT * FROM tablename WHERE active;

Alternatively, standard comparison syntax can be employed:

SELECT * FROM tablename WHERE active = TRUE;

For display conversion in SELECT statements, the following pattern is recommended:

SELECT 
  CASE WHEN active = TRUE THEN 'TRUE' ELSE 'FALSE' END AS active_status,
  ...other columns...
FROM tablename
WHERE active = TRUE;

A complete example illustrates this approach more clearly:

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;

Executing this query produces the following results:

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

Alternative Approach Using Type Casting

Beyond CASE statements, type casting operators can convert boolean values to text representations. This method is more concise but requires attention to output case formatting:

SELECT TRUE::TEXT AS t, FALSE::TEXT AS f;

The above query returns lowercase "true" and "false." For uppercase format, combine with the UPPER function:

SELECT UPPER(TRUE::TEXT) AS t, UPPER(FALSE::TEXT) AS f;

It is important to note that the textual representation of boolean values actually depends on the client library used. For instance, the JDBC driver renders boolean values as 'true'/'false' by default, without requiring additional conversion operations.

Improvements in PostgreSQL 9.5

PostgreSQL version 9.5 introduced a significant behavioral change, particularly within PL/pgSQL environments. According to the official release notes, this version switched to using assignment cast behavior for data type conversions instead of the previous text conversion approach.

This change brought a notable improvement: boolean-to-string conversions now produce 'true' or 'false' instead of 't' or 'f.' This means that within PL/pgSQL code, string representations of boolean values align more closely with expectations and standards.

However, this improvement primarily affects PL/pgSQL environments. For regular SQL queries, especially those displayed in pgAdmin's SQL editor, the behavior may still show 't'/'f' format. Therefore, the conversion methods described above remain necessary in most scenarios.

Best Practice Recommendations

Based on an in-depth analysis of PostgreSQL's boolean handling mechanisms, we propose the following best practice recommendations:

First, in application development, consistently use TRUE and FALSE keywords, as this adheres to SQL standards and enhances code readability. Avoid using 't', 'f', or other non-standard representations.

Second, for scenarios requiring specific output formats, CASE statements offer maximum flexibility and control. Although additional coding is required, this ensures consistent and predictable outputs.

Third, understanding behavioral differences across client tools is crucial. The inconsistency between pgAdmin's SQL editor and object browser reminds us not to rely on default tool behaviors but to enforce desired formats through explicit conversions.

Finally, consider upgrading to newer PostgreSQL versions. While the 9.5 improvements mainly target PL/pgSQL, newer versions typically include more standards compliance enhancements and performance optimizations.

By adopting these practices, developers can ensure that boolean value handling in PostgreSQL both conforms to standards and meets specific application needs, avoiding issues caused by inconsistent display formats.