Re: implicit cast works for insert, not for select

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