"robertlazarski ." <robertlazarski@gmail.com> writes:
> The biggest problem has been the tiny int boolean that SQL Server
> uses, which I can get to work for postgres inserts by:
> atdev=# update pg_cast set castcontext = 'a' where castsource =
> 'int'::regtype and casttarget = 'bool'::regtype;
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.
> That allows me to apply the DDL and all is well, until I do this
> select (auto generated by hibernate) :
> atdev=# select atsettings0_.atSettingsID as atSettin1_12_,
> atsettings0_.OBJ_VERSION as OBJ2_12_, atsettings0_.name as name12_,
> atsettings0_.value as value12_, atsettings0_.description as
> descript5_12_, atsettings0_.enabled as enabled12_,
> atsettings0_.deleted as deleted12_ from ATSettings atsettings0_ where
> (atsettings0_."deleted" = 0 OR atsettings0_."deleted" IS NULL ) and
> atsettings0_.atSettingsID=1;
> ERROR: operator does not exist: boolean = integer
> LINE 1: ...ttings atsettings0_ where (atsettings0_."deleted" = 0 OR ats...
> ^
> 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