Обсуждение: 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