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

From Adrian Klaver
Subject Re: mssql migration and boolean to integer problems
Date
Msg-id 200712141846.17286.aklaver@comcast.net
Whole thread Raw
In response to mssql migration and boolean to integer problems  (robert <robertlazarski@gmail.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Julian Day 0 question
Next
From: Adrian Klaver
Date:
Subject: Re: Finding bad bye in "invalid byte sequence" error