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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
