Enabling Implicit Cast From Integer To Boolean in PostgreSQL
I’ve been using MySQL for a while with my pet projects, but recently I’ve been writing some more complicated queries, and I’m running into a few obnoxious limitations, mostly involving known view performance problems. I’ve decided to start testing the waters in PostgreSQL, so I tried importing my data into PG by running a MySQL-generated insert statement, only to run into a PostgreSQL newbie gotcha; “Column
By default, PG does not automatically interpret “1″ or “0″ as a boolean. The arguments in favor of this limitation are that it prevents “unintended” conversions. However, I’ve been developing in Python for years, which does do this automatic conversion, and its never bitten me.
The code to disable this feature is relatively simple, although I can’t find anyone who’s mentioned it, so I post it here:
UPDATE pg_cast SET castcontext = 'i' WHERE oid IN ( SELECT c.oid FROM pg_cast c inner join pg_type src ON src.oid = c.castsource inner join pg_type tgt ON tgt.oid = c.casttarget WHERE src.typname LIKE 'int%' AND tgt.typname LIKE 'bool%' )
This SQL updates PG’s int-to-bool cast path to “implicit” mode, meaning PG will now automatically cast an integer to boolean if the target type is a boolean. Otherwise, in the default “emplicit” mode, you’d have to use the CAST() syntax.
After the update, the insert statement runs perfectly.
Filed under: PostgreSQL
Thanks for this post, I found it by googling an error message I got while trying to do exactly this type of implicit cast and your code worked like a charm.
Besides “e” and “i” are there other options for “castcontext”?
I’m glad you found it useful.
According to http://doxygen.postgresql.org/pg__cast_8h-source.html the three allowable values for pg_cast.castcontext are “e”, “i”, and “a”, where “a” specifies “coercion in context of assignment”.
Is it possible to do the same thing between text(widh defined length, or char) and int??
Thanks, Stefan
@Stefan
PG contains an explicit path from int to char and char to int. However, even after I changed this to implicit mode, PG still throws the conversion error “operator does not exist: integer = character” for a simple query like: SELECT NULL WHERE 0=(’0′::CHAR). I’m not sure why this path is being treated differently then int-to-bool.
However, it’s important to note that based on your application, making this change could be dangerous. It might be safer (and easier) to just use the explicit ::int cast on a per-use basis instead of changing database-wide functionality.