Re: mssql migration and boolean to integer problems

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