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

For database engineers and architects designing schemas, it required workarounds to represent "true" or "false" values as ‘Y’/’N’, ‘T’/’F’, or 1/0. While these may function adequately on the surface, they may hinder performance. The lack of a native Boolean data type can be a limitation in database design and impact storage efficiency.

The Workarounds We Lived With

Before Oracle’s recent version 23ai introduced BOOLEAN as the supported datatypes, following were some of the options.

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.

Contact Us Today!

Contact HexaCluster today ! Please enable JavaScript in your browser to complete this form. Database and Application Migrations Oracle to PostgreSQL SQL Server to PostgreSQL MySQL/MariaDB to PostgreSQL Application Migration & Modernization PostgreSQL Services Architectural Health Audit Managed DBA Services Performance Tuning & Development 24/7 PostgreSQL Support Name * Company Email * Submit