Oracle’s adoption of Native Boolean Data Type vs PostgreSQL

Oracle has finally introduced support for the Boolean data type in the release 23ai. Many thanks to the Engineers at Oracle for implementing this data type for an optimal performance. PL/SQL had BOOLEAN for decades, but developers were not able to declare native boolean type for columns of tables. For this reason, developers returned VARCHAR2/NUMBER from functions instead of BOOLEAN. Interestingly, PostgreSQL, an open-source relational database that has been widely used for many years and a migration target for Oracle, has had support for Boolean data for more than the past two decades. In this article, we will discuss about the workarounds used by developers before Oracle adopted boolean, and how it works in PostgreSQL.

The Hidden Cost of Simulating True and False

The Workarounds We Lived With

Each of them may add redundant conversions and conditions in application code and PL/SQL functions, leading to more CPU work and larger indexes.

While Oracle took 2 decades to bring this up, PostgreSQL had it forever. Postgres stores Boolean values efficiently (internally as a single byte) and allows direct logical operations as follows.

SELECT * FROM employees WHERE is_active;

UPDATE orders SET is_verified = TRUE WHERE id = 1001;

What this clearly means is that it requires - No conversions, no string comparisons, just clean, logical semantics. This approach results in simpler queries, smaller indexes, and faster filtering, especially in analytical workloads with millions of rows.

Following chart demonstrates us the table structure in both Oracle and PostgreSQL, where we can avoid the additional checks in the case of PostgreSQL but not in Oracle.

CREATE TABLE user_flags (
   user_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   is_active   CHAR(1) CHECK (is_active IN ('Y', 'N')),
   is_verified NUMBER(1) CHECK (is_verified IN (0, 1))
);
        

CREATE TABLE user_flags (
   user_id     INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   is_active   BOOLEAN,
   is_verified BOOLEAN
);
        

Insert Statements

You can directly insert TRUE and FALSE if boolean is supported natively, as seen in the case of PostgreSQL.

INSERT INTO user_flags (is_active, is_verified)
VALUES ('Y', 1);
INSERT INTO user_flags (is_active, is_verified)
VALUES ('N', 0);

INSERT INTO user_flags (is_active, is_verified)
VALUES (TRUE, TRUE);

INSERT INTO user_flags (is_active, is_verified)
VALUES (FALSE, FALSE);

Select Statements

You can see simplified and performant select statements as seen in the table below.

SQL> SELECT * FROM genericmini_cdc.user_flags 
WHERE is_active = 'Y' AND is_verified = 1;
USER_ID | IS_ACTIVE | IS_VERIFIED
--------+-----------+------------
1       | Y         | 1
2       | Y         | 1

postgres=# SELECT * FROM user_flags
WHERE is_active AND is_verified;

user_id | is_active | is_verified
--------+-----------+------------
1       | t         | t
(1 row)

In a nutshell, we can see boolean as the optimal data type over other relevant alternatives. Some of such benefits are listed in the table below.

To simplify end-to-end database migrations from Oracle to PostgreSQL, we have announced our tool called Hexarocket. One of the advantages of using this tool is that - during data migration from Oracle to PostgreSQL, it can automatically map CHAR(1) and NUMBER(1) of Oracle to BOOLEAN in PostgreSQL.

Are you looking to migrate from Oracle to PostgreSQL or SQL Server to PostgreSQL? We are here to support with a simple and seamless migration experience within few clicks using HexaRocket. Request us for a demo on HexaRocket today: Schedule a demo.