Thread: implicit cast works for insert, not for select
I am migrating a DB from SQL Server to Postgres 9.2.7 on Centos 7, via
regex converting the SQL Server DDL to a Postgres DDL. Both DB's need
to be supported in the near term.
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;
atdev=# create table foo (f1 bool);
CREATE TABLE
atdev=# insert into foo values(1);
INSERT 0 1
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.
If I quote the zero as:
= '0'
Then that would work, but since this 'deleted' column is a boolean
type for a hibernate generated query that works fine in SQL Server, I
would really like some type of cast here to make the above select work
as is. Any ideas?
On 01/22/2015 02:31 AM, robertlazarski . wrote: > I am migrating a DB from SQL Server to Postgres 9.2.7 on Centos 7, via > regex converting the SQL Server DDL to a Postgres DDL. Both DB's need > to be supported in the near term. > > 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; Well if I am following the below correctly: http://www.postgresql.org/docs/9.3/interactive/catalog-pg-cast.html castcontext char Indicates what contexts the cast can be invoked in. e means only as an explicit cast (using CAST or :: syntax). a means implicitly in assignment to a target column, as well as explicitly. i means implicitly in expressions, as well as the other cases. you should be setting castcontext = 'i' > > atdev=# create table foo (f1 bool); > CREATE TABLE > atdev=# insert into foo values(1); > INSERT 0 1 > > 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. > > If I quote the zero as: > > = '0' > > Then that would work, but since this 'deleted' column is a boolean > type for a hibernate generated query that works fine in SQL Server, I > would really like some type of cast here to make the above select work > as is. Any ideas? > > -- Adrian Klaver adrian.klaver@aklaver.com
"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
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.