# The PostgreSQL® Boolean Three-Valued Logic Data Type
[Blog](/blog/)>[Technology](/blog/category/technical/)>The PostgreSQL® Boolean Three-Valued Logic Data Type
The PostgreSQL® Boolean Three-Valued Logic Data Type
====================================================
June 01, 2021 | By [ Paul Brebner](https://www.instaclustr.com/blog/author/paul-brebner/)
[ ](https://x.com/intent/tweet?text=The%20PostgreSQL%C2%AE%20Boolean%20Three-Valued%20Logic%20Data%20Type&url=https://www.instaclustr.com/blog/the-postgresql-boolean-three-valued-logic-data-type/) [ ](https://www.linkedin.com/shareArticle?mini=true&url=https://www.instaclustr.com/blog/the-postgresql-boolean-three-valued-logic-data-type/&title=&summary=The%20PostgreSQL%C2%AE%20Boolean%20Three-Valued%20Logic%20Data%20Type&source=)
In my previous [PostgreSQL blog](https://www.instaclustr.com/blog/postgresql-data-types-mappings-to-sql-jdbc-and-java-data-types/), we discovered what data types are available in PostgreSQL (a lot) and hopefully determined the definitive mapping from [PostgreSQL](https://www.instaclustr.com/blog/the-case-for-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
PostgreSQL boolean is Trinary/Ternary/Trivalent/3VL/TVL, not Binary
*(Source:* Shutterstock*)*
It was interesting to [read](https://www.postgresql.org/docs/current/datatype-boolean.html) that the [PostgreSQL](https://www.instaclustr.com/support-solutions/) 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 ](https://modern-sql.com/concept/three-valued-logic)(also called [Trinary](https://www.researchgate.net/publication/235329576_Trinary_logic_20), [Ternary](https://en.wikipedia.org/wiki/Ternary_computer), [Trivalent](https://plato.stanford.edu/entries/logic-manyvalued/), [3VL](https://modern-sql.com/concept/three-valued-logic), etc. logic) and supports the standard logical operators (AND, OR, NOT) but with [different truth tables](https://www.postgresql.org/docs/current/functions-logical.html) to take into account the “unknown” value.
Apparently, [the intent of NULL in SQL](https://en.wikipedia.org/wiki/Three-valued_logic) 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**TRUETRUETRUETRUEFALSETRUEFALSEFALSETRUEFALSETRUEUNKNOWN**UNKNOWN**TRUEFALSEFALSETRUEFALSETRUETRUEFALSEFALSEFALSEFALSETRUEFALSEUNKNOWNFALSE**UNKNOWN**TRUEUNKNOWNTRUE**UNKNOWN**TRUE**UNKNOWN**UNKNOWNFALSEFALSE**UNKNOWN****UNKNOWN**UNKNOWNUNKNOWN**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](https://www.instaclustr.com/blog/postgresql-data-types-mappings-to-sql-jdbc-and-java-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
*(Source: Shutterstock)*
The TVL (Three-Valued-Logic) class is just an enum with the three possible states:
```
public static enum TVL {
TRUE,
FALSE,
UNKNOWN;
// functions below are in the enum
. . .
}
```
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:
```
public static TVL fromString(String x)
{
if (x == null)
return TVL.UNKNOWN;
else
if (x.contentEquals("t"))
return TVL.TRUE;
else // if (x.contentEquals("f"))
return TVL.FALSE;
}
```
Using this test2 table:
```
CREATE TABLE test2 (
id integer PRIMARY KEY;
value boolean;
);
```
The function can be used as follows:
```
// assuming we have created a row for id=100
pst = conn.prepareStatement("SELECT value FROM test2 WHERE id = ?");
pst.setInt(1, 100);
rs = pst.executeQuery();
while (rs.next())
{
System.out.println("boolean TVL value = " + TVL.fromString(rs.getString("value")));
}
```
I also wrote this function to convert from TVL to PostgreSQL boolean Strings:
```
public String toPGBoolString()
{
if (this == TVL.TRUE)
return "t";
else if (this == TVL.FALSE)
return "f";
else return null;
}
```
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():
```
pst = conn.prepareStatement("INSERT INTO test2(id, value) VALUES (?, ?)");
pst.setInt(1, 101);
pst.setString(TVL.UNKNOWN.toPGBoolString()); // error, can’t set String on boolean data type
```
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:
```
rs = st.executeQuery("INSERT INTO test2 VALUES (101," + TVL.UNKNOWN.toPGBoolString()+ ")");
```
[Here’s](https://gist.github.com/paul-brebner/cf9324acf565c4d243181d0c5cc2f437.js) 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!
*(Source: Shutterstock)*
### About the author
**[Paul Brebner](https://www.instaclustr.com/blog/author/paul-brebner/)** | Technology Evangelist at Instaclustr
Paul has extensive R&D and consulting experience in distributed systems, technology innovation, software architecture, and engineering, software performance and scalability, grid and cloud computing, and data analytics and machine learning.
[ Add Instaclustr as a preferred source on Google ](https://google.com/preferences/source?q=instaclustr.com)
![mail icon]()#### Get the latest articles for open sourceIn your inbox
### Related content
[ Kafka® Connect and Elasticsearch™ vs. PostgreSQL® Pipelines: Initial Performance Results (Pipeline Series Part 8)
In Part 6 and Part 7 of the pipeline series we took a different path in the pipe/tunnel and explored PostgreSQL and Apache ...
](https://www.instaclustr.com/blog/kafka-connect-elasticsearch-pipeline-series-part-8/)
[ PostgreSQL® 14: PostgreSQL Is Still a Teenager
PostgreSQL 14 release brings about many improvemeents but that's it. In this blog,Kirk Roybal, talks the focus of this new update...
](https://www.instaclustr.com/blog/postgresql-14/)
[ The Case for PostgreSQL®
In this blog post by Instaclustr, we explore the value of PostgreSQL for database administration and explain why you should learn ...
](https://www.instaclustr.com/blog/the-case-for-postgresql/)
×Sign upto ourNewsletter
-----------------------