Thread: How to convert integer to boolean in insert

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?

Thanks
--
Miao

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

On Sun, Apr 05, 2009 at 04:14:37AM -0700, JiangMiao wrote:
> 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?

You probably don't want implicit, since that will probably trip you up
somewhere else. You want an assignment cast. The fucntion you want
exists, called 'bool'.

# select castfunc::regproc from pg_cast where castsource = 'int4'::regtype and casttarget = 'boolean'::regtype;
 castfunc
----------
 bool
(1 row)

You might want to create the cast temporarily and after the import
remove it again.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Martijn van Oosterhout <kleptog@svana.org> writes:
> On Sun, Apr 05, 2009 at 04:14:37AM -0700, JiangMiao wrote:
>> Is there any way to make pgsql implicit cast the integer to boolean?

> You might want to create the cast temporarily and after the import
> remove it again.

The cast exists already, so he can't just "create" it.  Personally, what
I'd do is modify the pg_cast entry for it (set castcontext to 'a' and
then revert when done).

            regards, tom lane

Thomas Kellerer wrote:

> 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?

Actually it seems it would be better to create the table with only the
integer column, and later use ALTER TABLE / TYPE to change it.
Something like

CREATE TABLE foo (banned integer);

-- run inserts

ALTER TABLE foo ALTER banned TYPE bool USING banned::bool;

That way you don't end up with half a table of dead tuples.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote on 06.04.2009 19:39:
>> 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?
>
> Actually it seems it would be better to create the table with only the
> integer column, and later use ALTER TABLE / TYPE to change it.
> Something like
>
> CREATE TABLE foo (banned integer);
>
> -- run inserts
>
> ALTER TABLE foo ALTER banned TYPE bool USING banned::bool;
>
> That way you don't end up with half a table of dead tuples.

Yes I was worrying about that as well.

Didn't know about the cast in the ALTER statement ;)

Pretty cool.

Thomas