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

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:

X	Y	X AND Y	X OR Y	NOT X
TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	UNKNOWN	UNKNOWN	TRUE	FALSE
FALSE	TRUE	FALSE	TRUE	TRUE
FALSE	FALSE	FALSE	FALSE	TRUE
FALSE	UNKNOWN	FALSE	UNKNOWN	TRUE
UNKNOWN	TRUE	UNKNOWN	TRUE	UNKNOWN
UNKNOWN	FALSE	FALSE	UNKNOWN	UNKNOWN
UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

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

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!