Re: mssql migration and boolean to integer problems - Mailing list pgsql-general

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