Re: How to convert integer to boolean in insert - Mailing list pgsql-general

JiangMiao wrote on 05.04.2009 13:14:
> for table
> foo
>   banned:boolean
>
> When try  run 'insert into foo(banned) values(0)'
> It returns
> ERROR:  column "banned" is of type boolean but expression is of type
> integer
> LINE 1:  insert into foo(banned) values(0)
>
> and I found a way to add the cast
> insert into foo(banned) values(0::boolean)
> but I have a big table which dump from mysqldump and covert by
> mysql2pgsql. all of boolean relation values is 0 instead of '0' or
> FALSE.
>
> Is there any way to make pgsql implicit cast the integer to boolean?

If that is a one-time thing, why not create the table with banned as an integer
column and another boolean column (if your INSERTs are properly listing the
column names), then after the import update the boolean to the casted integer
value, drop the integer and rename the boolean?

Something like:

CREATE TABLE foo (banned integer, banned_b boolean);

-- run your inserts

update foo set banned_b =
                 case banned
                   when 0 then false
                   else true
                 end;

alter table foo drop column banned;
alter table foo rename column banned_b to banned;

Thomas