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.

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.

QueryOutput
select 1 where 1::boolean1
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.


About Deepak Mahto

Database Guy with expertise in database performance, migration and Cloud Adoption.
This entry was posted in Oracle to, Oracle to PG migration, postgresql and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s