Обсуждение: mssql migration and boolean to integer problems
Hi all, I've spent the last few days hacking a mssql INSERT script to work with 8.1.9 - I could build the latest postgres source if need be. My latest problem is: ERROR: column "includeScenario" is of type boolean but expression is of type integer HINT: You will need to rewrite or cast the expression. So mssql uses tiny int for booleans, and I have about 50 of those ;-) . I googled alot on this, and tried 4 or 5 different ideas with Functions and alter tables - but I can't find anything that's working with 8.1.9, can someone please help me? Thanks, Robert
On Dec 12, 11:09 pm, robert <robertlazar...@gmail.com> wrote: > Hi all, I've spent the last few days hacking a mssql INSERT script to > work with 8.1.9 - I could build the latest postgres source if need be. > My latest problem is: > > ERROR: column "includeScenario" is of type boolean but expression is > of type integer > HINT: You will need to rewrite or cast the expression. > > So mssql uses tiny int for booleans, and I have about 50 of > those ;-) . I googled alot on this, and tried 4 or 5 different ideas > with Functions and alter tables - but I can't find anything that's > working with 8.1.9, can someone please help me? > > Thanks, > Robert Really stuck, please help. I have this table: create table "ASSETSCENARIO" ("assetScenarioID" int8 not null, OBJ_VERSION int8 not null, "includeScenario" bool, "scenarioName" varchar(2000), "probability" int8, "occurenceDate" timestamp, "notes" varchar(2000), "priceTarget" float8, "assetID" int8 not null, "created" timestamp not null, "modified" timestamp not null, "createdUserID" int8 not null, "modifiedUserID" int8 not null, "deleted" bool, primary key ("assetScenarioID")); So it has two 'bool' - "includeScenario" and "deleted" . I have an insert like... INSERT INTO "ASSETSCENARIO" ("assetScenarioID",OBJ_VERSION,"includeScenario","scenarioName","probability","occurenceDate","notes","priceTarget","assetID","created","modified","createdUserID","modifiedUserID","deleted")VALUES(197,0,1,'2007-12-13 11:31:00.000','2007-12-13 11:31:00.000',2,2,NULL); I've tried: CREATE FUNCTION boolean_integer_compare(boolean,integer) RETURNS boolean AS $$ SELECT ($2 = 1 AND $1) OR ($2 = 0 AND NOT $1); $$ LANGUAGE SQL; CREATE OPERATOR = ( leftarg = boolean, rightarg = integer, procedure = boolean_integer_compare, commutator = = ); And alternatively: CREATE FUNCTION notinttobool(integer, boolean) RETURNS boolean AS ' begin return not inttobool($1,$2); end; ' LANGUAGE plpgsql; CREATE OPERATOR = ( PROCEDURE = inttobool, LEFTARG = boolean, RIGHTARG = integer, COMMUTATOR = =, NEGATOR = <> ); CREATE OPERATOR <> ( PROCEDURE = notinttobool, LEFTARG = integer, RIGHTARG = boolean, COMMUTATOR = <>, NEGATOR = = ); CREATE OPERATOR = ( PROCEDURE = inttobool, LEFTARG = integer, RIGHTARG = boolean, COMMUTATOR = =, NEGATOR = <> ); CREATE OPERATOR <> ( PROCEDURE = notinttobool, LEFTARG = boolean, RIGHTARG = integer, COMMUTATOR = <>, NEGATOR = = ); Lastly, I tried: ALTER TABLE table ALTER COLUMN field1 TYPE boolean USING CASE WHEN field1 = 0 THEN FALSE WHEN field1 = 1 THEN TRUE ELSE NULL END; Each time I get: ERROR: column "includeScenario" is of type boolean but expression is of type integer HINT: You will need to rewrite or cast the expression. Right now I'm trying to "cast the expression." - how do I do that in this case? Thanks, Robert
--- On Thu, 12/13/07, robert <robertlazarski@gmail.com> wrote: > > Hi all, I've spent the last few days hacking a > mssql INSERT script to > > work with 8.1.9 - I could build the latest postgres > source if need be. a standard cast() wouldn't work for you? proj02u20411=> select cast( 1 as boolean), proj02u20411=> cast( 0 as boolean), proj02u20411=> cast( -1 as boolean ), proj02u20411=> cast( 2 as boolean); bool | bool | bool | bool ------+------+------+------ t | f | t | t Regards, Richard Broersma Jr.
"robert" <robertlazarski@gmail.com> writes: > So it has two 'bool' - "includeScenario" and "deleted" . I have an > insert like... > > INSERT INTO "ASSETSCENARIO" > ("assetScenarioID",OBJ_VERSION,"includeScenario","scenarioName","probability","occurenceDate","notes","priceTarget","assetID","created","modified","createdUserID","modifiedUserID","deleted") > VALUES > (197,0,1,'2007-12-13 11:31:00.000','2007-12-13 11:31:00.000',2,2,NULL); There's an SQL standard syntax too, but the Postgres-specific syntax is: postgres=# select 1::bool; bool ------ t (1 row) postgres=# select 0::bool; bool ------ f (1 row) Alternatively you could just quote the inputs. If you insert '0' and '1' they'll be parsed as boolean values. It's just because you used 0 and 1 without quotes that they're parsed as integers first then don't match the boolean type. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
On Wednesday 12 December 2007 8:09 pm, robert wrote: > Hi all, I've spent the last few days hacking a mssql INSERT script to > work with 8.1.9 - I could build the latest postgres source if need be. > My latest problem is: > > ERROR: column "includeScenario" is of type boolean but expression is > of type integer > HINT: You will need to rewrite or cast the expression. > > So mssql uses tiny int for booleans, and I have about 50 of > those ;-) . I googled alot on this, and tried 4 or 5 different ideas > with Functions and alter tables - but I can't find anything that's > working with 8.1.9, can someone please help me? > In 8.2 there is a built in int --> bool cast. I had a similiar problem with 8.0 and I created my own int::bool cast using the following: CREATE CAST (int4 AS bool) WITH FUNCTION bool(int4) AS ASSIGNMENT; CREATE OR REPLACE FUNCTION bool(int4) RETURNS bool AS $Body$ Declare output char(1); Begin Select into output $1; Return output; End; $Body$ LANGUAGE 'plpgsql' VOLATILE; This way I did not have to include the casting in SQL statements. Be aware that if you upgrade to 8.2 the restore process will weed out the above because of the builtin cast. -- Adrian Klaver aklaver@comcast.net
On Dec 14, 2007 12:49 PM, Gregory Stark <stark@enterprisedb.com> wrote: > > "robert" <robertlazarski@gmail.com> writes: > > > So it has two 'bool' - "includeScenario" and "deleted" . I have an > > insert like... > > > > INSERT INTO "ASSETSCENARIO" > > ("assetScenarioID",OBJ_VERSION,"includeScenario","scenarioName","probability","occurenceDate","notes","priceTarget","assetID","created","modified","createdUserID","modifiedUserID","deleted") > > VALUES > > (197,0,1,'2007-12-13 11:31:00.000','2007-12-13 11:31:00.000',2,2,NULL); > > There's an SQL standard syntax too, but the Postgres-specific syntax is: > > postgres=# select 1::bool; > bool > ------ > t > (1 row) > > postgres=# select 0::bool; > bool > ------ > f > (1 row) > > > Alternatively you could just quote the inputs. If you insert '0' and '1' > they'll be parsed as boolean values. It's just because you used 0 and 1 > without quotes that they're parsed as integers first then don't match the > boolean type. > Is there any way to get the above insert to work as is, via a function or some other way? I tried the function of another poster but it seems there is already a cast built in for 8.1.9 for integer to boolean, and it didn't work for me on the above insert. I'm using Java and Hibernate so I don't control the select so I wouldn't be able to cast on it AFAIK. I've gotten this far using the mssql inserts by tweaking them via regular expressions. The problem I have with putting quotes around the values such as '0' and '1' is that would be a seem to me to be a hard search and replace expression to write, as 0 and 1 is so common, the booleans are scattered around a lot, and there's no indication in the inserts file to indicate what fields are booleans. What I'm trying to avoid is just using integer for these values instead of boolean - that would work but I'd have to rewrite a fair amount of java code to do do that. Thanks for any further ideas, Robert
"robert lazarski" <robertlazarski@gmail.com> writes: > Is there any way to get the above insert to work as is, via a function > or some other way? I tried the function of another poster but it seems > there is already a cast built in for 8.1.9 for integer to boolean, and > it didn't work for me on the above insert. You could mark the built-in cast as assignment-only (I wouldn't recommend setting it to implicit, as it's not clear what cases that might break). d1=# create table foo (f1 bool); CREATE TABLE d1=# insert into foo values(1); ERROR: column "f1" is of type boolean but expression is of type integer HINT: You will need to rewrite or cast the expression. d1=# update pg_cast set castcontext = 'a' where castsource = 'int'::regtype d1-# and casttarget = 'bool'::regtype; UPDATE 1 d1=# insert into foo values(1); INSERT 0 1 Unfortunately this is something you'd have to do over after any database reload, because pg_dump won't preserve changes to the definitions of built-in objects. regards, tom lane
On Dec 17, 2007 12:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "robert lazarski" <robertlazarski@gmail.com> writes: > > Is there any way to get the above insert to work as is, via a function > > or some other way? I tried the function of another poster but it seems > > there is already a cast built in for 8.1.9 for integer to boolean, and > > it didn't work for me on the above insert. > > You could mark the built-in cast as assignment-only (I wouldn't > recommend setting it to implicit, as it's not clear what cases > that might break). > > d1=# create table foo (f1 bool); > CREATE TABLE > d1=# insert into foo values(1); > ERROR: column "f1" is of type boolean but expression is of type integer > HINT: You will need to rewrite or cast the expression. > d1=# update pg_cast set castcontext = 'a' where castsource = 'int'::regtype > d1-# and casttarget = 'bool'::regtype; > UPDATE 1 > d1=# insert into foo values(1); > INSERT 0 1 > > Unfortunately this is something you'd have to do over after any database > reload, because pg_dump won't preserve changes to the definitions of > built-in objects. > > regards, tom lane > Snoopy dance <http://www.google.com/search?q=snoopy+dance> :-) That worked great!!! Kind regards, Robert