On 01/29/2014 08:03 PM, Florian Weimer wrote:
> On 01/29/2014 09:07 AM, Craig Ringer wrote:
>
>> A challenge I've found when approaching this from the ORM side has been
>> getting people to care. The sentiment has tended to be along the lines
>> of: No other DBMS does this or requires this, why do we have to jump
>> through hoops just to make PostgreSQL happy?
>
> Is this true? Can you use other JDBC drivers (except SQLite) to insert
> Java Strings into NUMERIC columns
That'll require direct JDBC tests, which I don't presently have time to
set up for the DBs of interest (downloading demo versions or finding
ones I can mess with, etc).
The following tests based on simple SQL expressions, using CASTs to
force interpretation of values as character-typed, should be informative
though.
> and Java ints into text columns?
I wasn't ever suggesting that; that's why I'm being quite specific about
referring to implicit casts *FROM* text. Not tested.
In the following tests the client interface used is JDBC, but it's used
to execute statements directly, not with bind params. So it's more like
running the statement in psql or equivalent; for that reason, CASTs are
used to force explicit types.
Oracle: Behaves much like PostgreSQL for its own custom types, and
SQL/XML, but permissive for NUMERIC and TIMESTAMP (appears to fit the
JDBC spec):
http://sqlfiddle.com/#!4/cc065/5
http://sqlfiddle.com/#!4/cc065/6
http://sqlfiddle.com/#!4/1adc6/4
MS SQL server 2012: permissive
http://sqlfiddle.com/#!6/57662/3
PostgreSQL: Strict
http://sqlfiddle.com/#!15/596f78/2
http://sqlfiddle.com/#!15/596f78/3
http://sqlfiddle.com/#!15/596f78/4
http://sqlfiddle.com/#!15/596f78/6
MySQL 5.5: Permissive (surprise!)
http://sqlfiddle.com/#!2/89152
I don't really care about dredging up DB2, Firebird, etc.
So of the set tested, we're the strictest, Oracle is next-strictest and
looks like it's as strict as it can be while remaining JDBC compliant. I
was surprised to see that it won't accept character literal input for
its XmlType and UriType.
Other servers are more permissive about inputs.
> What about using types on the PostgreSQL side which match the
> application types?
So, unless your language has a native json type, or standard json
library that the PostgreSQL client driver can rely on being used, you
shouldn't be able to use json in PostgreSQL?
Not convinced.
> In any case, use *can* use strings everywhere if you use the
> stringtype=unspecified connection parameter:
>
> <http://jdbc.postgresql.org/documentation/92/connect.html#connection-parameters>
Yes, as I mentioned upthread, this is true for JDBC with the caveat that
it'll cause problems with function overload resolution.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services