chrisspenblog

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 is of type boolean but expression is of type integer”.

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