Technical Technical — Postgres Tuesday 1st June 2021

The PostgreSQL Boolean Three-Valued Logic Data Type

By Paul Brebner

In my previous PostgreSQL blog, we discovered what data types are available in PostgreSQL (a lot) and hopefully determined the definitive mapping from PostgreSQL to SQL/JDBC to Java data types. However, even armed with this information you have to be careful about type conversion/casting, and watch out for run-time errors, truncation, or loss of information.

But surely a really simple type such as bit, bool, and boolean should be idiot proof? True? (or False or Unknown…). Let’s try and see.

1. PostgreSQL Boolean “Three-Valued” Logic

Postgres Boolean
PostgreSQL boolean is Trinary/Ternary/Trivalent/3VL/TVL, not Binary
(Source: Shutterstock)

It was interesting to read that the PostgreSQL boolean (alias “bool”) data type is actually Trinary not Binary and therefore has three possible states: TRUE, FALSE, and “unknown” (represented by a NULL). This is evidently the standard SQL three-valued logic system (also called Trinary, Ternary, Trivalent, 3VL, etc. logic) and supports the standard logical operators (AND, OR, NOT) but with different truth tables to take into account the “unknown” value. 

Apparently, the intent of NULL in SQL is to represent missing data in the database—the assumption is that the actual value exists somewhere, but is not currently recorded in the database. Some interesting things to note about the three-valued logic truth tables is that TRUE AND UNKNOWN is UNKNOWN, TRUE OR UNKNOWN is TRUE, and NOT UNKNOWN is UNKNOWN. All the UNKNOWN results are highlighted in the full table below:

XYX AND YX OR YNOT X
TRUETRUETRUETRUEFALSE
TRUEFALSEFALSETRUEFALSE
TRUEUNKNOWNUNKNOWNTRUEFALSE
FALSETRUEFALSETRUETRUE
FALSEFALSEFALSEFALSETRUE
FALSEUNKNOWNFALSEUNKNOWNTRUE
UNKNOWNTRUEUNKNOWNTRUEUNKNOWN
UNKNOWNFALSEFALSEUNKNOWNUNKNOWN
UNKNOWNUNKNOWNUNKNOWNUNKNOWNUNKNOWN

Table 4: Three-Valued-Logic (AND, OR, NOT Operators)

Given the three-valued nature of the PostgreSQL boolean data type, it’s therefore surprising to find that it maps to Java boolean (Table 3 PostgreSQL Data Types), which is definitely only a two-valued binary logic system. How does this work? Well, using setBoolean() you can only INSERT TRUE and FALSE into PostgreSQL, and using getBoolean(), even though SELECT can return NULL as a value for a boolean column, it’s automatically converted to a Java false value, so you lose information in the conversion. 

Consequently, this means you can’t use Three-Valued logic operators on the results either. I decided to implement a simple Three-Value-Logic Java solution to get around these limitations and to see how well it works. 

2. Java “Three-Valued” Logic

Three-Valued-Logic
(Source: Shutterstock)

The TVL (Three-Valued-Logic) class is just an enum with the three possible states:

You can get the result of a SELECT on a boolean column with rs.getString(“value”) which returns the values “t”, “f” and a null String for NULL, so it’s easy to convert PostgreSQL boolean to TVL with this function:

Using this test2 table:

The function can be used as follows:

I also wrote this function to convert from TVL to PostgreSQL boolean Strings:

However, you can’t use this function directly to set the value in a prepared statement, as you get an error if you try something like this using setString():

You also can’t just use pst.setBoolean(1, null) to set the value to UNKNOWN. The only workaround I could think of was to call the function in an INSERT Statement as follows:

Here’s the complete TVL code including the logical operations:

So that’s my simple Three-Valued-Logic Java implementation to overcome some of the limitations of using the default PostgreSQL Java boolean mapping.

Have fun with “Tricycle” Logic!

Postgres
(Source: Shutterstock)