Re: PostgreSQL specific datatypes very confusing for beginners who use wrappers around JDBC

I've had the same problem as well with NHibernate (On .NET) with Postgres
ENUM types. Luckily, NHibernate is incredibly powerful and you *can* get
everything working flawlessly, however it takes some serious digging into
the source code and reading the docs to figure it out. The main issue is
that NHibernate, out of the box, wants to map an ENUM as a number. For
example:

INSERT INTO FOO SomeEnumColumn VALUES (1);

This will cause an error, because PG is looking for a string value (Even
though ENUMs are stored as numeric values under the covers). It's pretty
easy to configure NHibernate to convert ENUMs to strings (there's tons of
blog posts on that).. However, this causes NHibernate to write:

INSERT INTO FOO SomeEnumColumn VALUES ('EnumValue'::text);

Which will also cause an error. I've found the only way around it is to
configure NHibernate to treat ENUMs as "Objects" which will simply generate:

INSERT INTO FOO SomeEnumColumn VALUES ('EnumValue'); -- No casting here,
yay!

This works. However, to agree with the original poster's point, if
Postgres could be a little more forgiving about values that could be
interpreted as correct (like an implicit cast between numeric and enum and
string and enum) then we wouldn't have these issues..

Mike

On Tue, Jan 28, 2014 at 1:37 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> On 1/28/2014 1:20 PM, Tom Lane wrote:
>
>> I think you can fix it by explicitly casting your placeholders, eg
>> "?::macaddr".
>>
>
> that might work for a wrapper that lets you roll your own SQL, but I
> thought he said one of these autogenerated SQL, taking it out of his
> control.
>
>
>
>
> --
> john r pierce 37N 122W
> somewhere on the middle of the left coast
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>