On Thu, Jan 22, 2015 at 12:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> You realize of course that you've set that to be an assignment cast,
> not an implicit cast as the title of your message suggests. So this
> only changes the behavior for assignment contexts, ie INSERT/UPDATE
> target values.
>
Oops, my intent was to make it implicit so it works permanently on not
just INSERT/UPDATE, but also SELECT. Thanks for the explanation as
that helped fixed the select. I just need to use:
atdev=# update pg_cast set castcontext = 'i' where castsource
='int'::regtype and casttarget = 'bool'::regtype;
>> HINT: No operator matches the given name and argument type(s). You
>> might need to add explicit type casts.
>
> Well, yeah. If you made int->bool be an implicit cast instead, this
> would work. The side-effects of that might be more painful than fixing
> your application would be, however. It's quite likely that other
> cases involving mixtures of int and bool, or operators/functions that
> exist for both types, would suddenly start throwing "ambiguous
> operator" errors.
>
> I wonder whether you've made sure that (a) you're using a current
> release of Hibernate, and (b) it knows that it's talking to Postgres
> and not SQL Server. The alleged advantage of ORMs is that they can
> adapt their queries to the target database. Fixing this sort of
> non-standard, non-portable query at the database level is entirely
> the wrong way to go about it, IMO.
>
> regards, tom lane
That was a query using the postgres hibernate dialect. It expects a
zero and one int for booleans like SQL Server does, because the
postgres db population was done that way. The equivalent SQL Server
hibernate generated query is quite different.
My thinking is that this cast is the only viable option, compared to
changing 0 and 1 to '0' and '1' a couple of million times in a big
DDL, on a couple hundred boolean columns. I'd need a special regex for
every table that'd be a nightmare to maintain.
So I'm good for now, thanks all.