| From: | "robertlazarski (dot)" <robertlazarski(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | implicit cast works for insert, not for select |
| Date: | 2015-01-22 10:31:37 |
| Message-ID: | CABpPLBVRQYcQVcNiBd2fcx-qSURJ-CTe7t7-JWdioiCeH6XVGg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Kyotaro HORIGUCHI | 2015-01-22 11:07:30 | Re: partitioning query planner almost always scans all tables |
| Previous Message | Craig Ringer | 2015-01-22 02:37:27 | Re: BDR Error restarted |