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.
|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|
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.