Craig Ringer <craig@postnewspapers.com.au> writes:
> I'm finding a few areas where PostgreSQL's refusal to implicitly cast
> from 'text' to another type is causing real problems, particularly when
> using the PgJDBC driver. I'd like to propose a couple of relaxations of
> the implicit cast rules for certain text-like types:
> - user-defined enums; and
> - xml
In my mind, enums are not even remotely text-like. I'm not sure about
the xml case.
> Can anybody show me a case where permitting implicit casts from text for
> enums, xml or json types might introduce an error or cause SQL with a
> mistake in it to execute instead of failing when it should?
We got rid of the implicit casts in the other direction for extremely
good reasons. Consult the archives from a few years back and note the
frequency of questions like "why is timestamp comparison behaving so
oddly", to which the answer was always "well, you constructed your query
in such a way that you are getting textual comparison, even though an
error would have been a lot saner". I'm very unexcited about
introducing a similar risk in this direction. It wouldn't manifest in
that particular way of course, but implicit cross-type-category casts
are a dangerous foot-gun. In general the problem is that you have a
fifty-fifty chance of guessing which set of semantics the user thought
he would get for an operation involving two incompatible datatypes.
In the long run it's less pain all around if you throw an error and
make the user insert a cast to clarify.
This might be justifiable for xml, but only because it's got such a
paucity of interesting operators to begin with, thanks to the SQL
committee's weird ideas about preferable syntax. I think it'd be an
exceedingly bad idea for enums.
This isn't the right list for discussing how to use the JDBC driver,
but I had the idea that it is possible to pass a parameter through
JDBC without specifying any particular type for it. That would work
the way you want thanks to the rules for resolving unknown-type
literals/parameters.
regards, tom lane
