Re: mssql migration and boolean to integer problems

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