August 18th, 2009, 11:51 AM
Automatically Casting From Integer to Boolean
I have a huge insert statement dumped from MySQL, which I'm trying to load into PG. However, I have boolean fields, and it seems PG doesn't know how to interpret a 1 or 0 as booleans, so the insert statement fails.
After a little research, I found this is actually a feature, and can be disabled, with something like:
CREATE CAST (int4 AS boolean) WITH FUNCTION bool(int4) AS ASSIGNMENT;
However, psql informs me that "ERROR: cast from type int4 to type pg_catalog.bool already exists".
If the int4->bool cast path already exists, then why is PG giving me an error when I try to insert a 1 or 0 into a boolean column? How do I correctly enable this cast path?
August 18th, 2009, 12:24 PM
How does your data looks like? Are you sure you only try to insert 0 and 1 as values? MySQL doesn't have a boolean datatype.
PostgreSQL doesn't have any problem with a 0 of 1 to represent a boolean false or true.
August 18th, 2009, 02:00 PM
Yes, I'm sure.
Originally Posted by pgFrank
And sorry to correct you, but you're absolutely wrong. PG's default cast path from int-to-bool is explicit, meaning trying to insert a 0 or 1 into a boolean column will result in an error.
It took me a while to figure it out, but this SQL corrects the problem:
update pg_cast set castcontext = 'i' where oid in (
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%'
August 18th, 2009, 02:40 PM
Works great on versions 8.2, 8.3 and 8.4. And I've never changed anything in pg_cast.
You could also take a look in the manual:
Maybe I'm wrong, but it always worked like this. And I'm working with PG on at least 10 different operating systems, from UNIX to Windows, this is how PG uses booleans when I'm working with it.
August 18th, 2009, 02:46 PM
And like I said, the default behavior requires and *explicit* cast...which is what you did...
August 18th, 2009, 02:51 PM
Just use quotes ' around the value, and you will be fine. You don't have to cast, unless the datatype is unknown or not quoted.
August 18th, 2009, 03:01 PM
Thanks, good to know.
My MySQL dump is over a Gig, and it doesn't quote its boolean values. Post-processing it isn't feasible since there's no way to tell the difference between a boolean and an integer in the statement. Having PG figure it out automatically with the implicit path is the only way to go.