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.
| Oracle Table Structure before 23ai | Equivalent PostgreSQL Table Structure |
|---|---|
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.
| Oracle Compatible Insert before 23ai | PostgreSQL Compatible Insert |
|---|---|
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.
| Oracle | PostgreSQL |
|---|---|
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.
| Aspect | BOOLEAN |
CHAR(1) / CHAR(5) |
SMALLINT / NUMBER(1) |
|---|---|---|---|
| Meaning | Explicit logical type with TRUE, FALSE, and NULL |
Textual convention (e.g., 'Y', 'N', 'YES', 'NO') |
Numeric convention (1 = true, 0 = false) |
| Storage | Typically 1 byte or bit | 1–5 bytes depending on length and encoding | 1–2 bytes |
| Type Safety | Accepts only logical truth values | May store invalid text (e.g., 'A', '?') |
May store non-logical numbers (e.g., 2, -1) |
| Query Simplicity | Supports direct logical operations (AND, NOT, IS TRUE) |
Requires explicit comparison (='Y') |
Requires explicit comparison (=1) |
| Value Interpretation | Automatically interprets TRUE, FALSE, T, F, YES, NO, 1, 0 (case-insensitive) |
Interpretation depends on convention and case sensitivity | Limited to numeric values; cannot represent textual forms |
| Readability | Self-explanatory and standardized | Convention-dependent and less portable | Convention-dependent and less intuitive |
| Language Integration | Maps directly to native Boolean types | Requires conversion logic | Requires conversion logic |
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!