#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    21
    Rep Power
    0

    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?

    Regards,
    Chris
  2. #2
  3. So help me Codd
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2008
    Location
    Brazil
    Posts
    362
    Rep Power
    25
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    21
    Rep Power
    0
    Originally Posted by pgFrank
    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.
    Yes, I'm sure.

    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:
    Code:
    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%'
    )
  6. #4
  7. So help me Codd
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2008
    Location
    Brazil
    Posts
    362
    Rep Power
    25
    Code:
    SELECT
    	0::bool;
    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:
    Valid literal values for the "true" state are:
    TRUE
    't'
    'true'
    'y'
    'yes'
    'on'
    '1'
    For the "false" state, the following values can be used:
    FALSE
    'f'
    'false'
    'n'
    'no'
    'off'
    '0'
    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.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    21
    Rep Power
    0
    And like I said, the default behavior requires and *explicit* cast...which is what you did...
  10. #6
  11. So help me Codd
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2008
    Location
    Brazil
    Posts
    362
    Rep Power
    25
    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.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2009
    Posts
    21
    Rep Power
    0
    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.

IMN logo majestic logo threadwatch logo seochat tools logo