Re: 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(dot)(dot)(dot)(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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(at)comcast(dot)net

On Dec 14, 2007 12:49 PM, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
>
> "robert" <robertlazarski(at)gmail(dot)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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "robert lazarski" <robertlazarski(at)gmail(dot)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