Unleasing Boolean data type casting in PostgreSQL.

Data type casting is an important aspect of working with databases. It is essential to understand the characteristics and behaviors of both implicit and explicit casting. PostgreSQL provides implicit casting for various operations, such as assignment and comparison, as well as explicit casting options. Using the psql command line, it is easy to access a list of all the supported casting options for each data type.

For example, in PostgreSQL 14, the BOOLEAN data type offers the following type casts.

With \dC we can get different casting available for a data type and whether it is implicit in nature or not.

Implicit? implies casting to which data type can happen automatically and in what scope for a boolean data type.

let’s understand will some more queries it better.

select 1 where 'YES';
select 1 where 'NO';

Query output should not come as surprise as character varying to boolean is supported as part of implicit cast. But it will surely create curiosity on what are the different variant that can be supported on boolean implicit casting.

Boolean in PostgreSQL is capable of accepting all of the specified values.

('Y','N'),
('TRUE', 'FALSE'),
('YES', 'NO') ,
(true, false),
('1', '0') ,
('ON', 'OFF')#PostgreSQL #Boolean

— Deepak Mahto (@mahtodeepak05) December 8, 2022

Supported character values are case insensitive and can be supported for implicit casting.

On casting from Integer to Boolean no implicit conversion is support. Though it is supported as explicit conversion with 0 as false and all positive integer as True. below tabular representaion facilitate characteristics on different values.

Query	Output
select 1 where 1::boolean	1
select 1 where 0::boolean;	(0 rows)
select 1 where -1::boolean;	ERROR:  operator does not exist: – boolean
select 1 where 2::boolean;	1

Tabular matrix – PostgreSQL integer to boolean cast

When migrating away from Oracle, Boolean implicit behaviour can be used to transform columns with character or varchar2 data type to Boolean in PostgreSQL. Newer version of Oracle has planned to add support for Boolean data type in SQL scope as compared to only being available for PL\SQL and for all previous version we can plan our migration with transformaton on columns to boolean is need arise.

Till then, below hold true ☺️

Ora2pg with BOOLEAN_VALUES and MODIFY_COLUMN configuration facilitate supporting column data type tranformaton when migrating away from Oracle.

Transforming values to Bool when using Ora2pg, you can use the BOOLEAN_VALUES setting to define accepted Boolean values, including their case sensitivity.
BOOLEAN_VALUES yes:no y:n 1:0 true:false enabled:disabled Y:N

It helps Ora2pg to recognize values as Bool and Convert. https://t.co/vjBkzufTDs

— Deepak Mahto (@mahtodeepak05) December 11, 2022