Re: [JDBC] getTypeInfo - Mailing list pgsql-jdbc

Hi Dave,

Looked at this and here is the query that we use:

"SELECT t.typname,t.oid FROM pg_catalog.pg_type t"
      + " JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) "
      + " WHERE n.nspname  != 'pg_toast'";

So we could order on typname, but there's no guarantee that it would line up with java.sqltypes

I do not think that order on TYPE_NAME would improve the compliance as the documentation talks about DATA_TYPE. Maybe a sort on the filled:

byte[][] tuple

by column 1 (DATA_TYPE) could be used? 

It is not nice to perform sorting in Java instead of in SQL, but I do not think that PostgreSQL is going to contain Java DATA_TYPE anywhere -> if we wanted to perform sorting in SQL, we would have to first upload DATA_TYPE and I am not sure it is worth that.

To make the ordering unique, the comparator could first compare by DATA_TYPE (has the priority) then by TYPE_NAME (TYPE_NAME is unique -> the ordering is unique).

The result is still not going to be perfect, because bigserial is going to be before int8, even thought range of int8 matches JDBC BIGINT better (https://www.postgresql.org/docs/9.6/static/datatype-numeric.html and http://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/mapping.html). And the documentation states:

...and then by how closely the data type maps to the corresponding JDBC SQL type.

Fortunately, PostgreSQL has only a few data types where the order can make a difference (DATA_TYPEs =< 93). Proposed ordering:

bit, boolean (because MySQL driver uses the same order)

int8, bigserial, oid (because BIGINT should be signed and oid is internal)

char, bchar (because bchar is internal)

int4, serial (because INTEGER should be signed)

float, money (because DOUBLE should be a floating point)

varchar, text, name (because VARCHAR has typically length up to 254 character and name is internal)

time, timetz (because TIME is without time zone)

timestamp, timestamptz (because TIMESTAMP is without time zone)

Best regards,

 Jan Motl

PS:

Oid data type should possibly not be classified as BIGINT but as INTEGER, because oid is using 4 bytes, not 8 bytes:

https://www.postgresql.org/docs/9.6/static/datatype-oid.html

To me, oid looks a lot like serial data type, which is classified as INTEGER.

Money data type should possibly not be classified as DOUBLE but as NUMERIC, because money is using fixed fractional precision.

Text data type should possibly not be classified as VARCHAR but as LONGVARCHAR, because text can be much longer than VARCHAR.

Boolean could be classified as BOOLEAN (introduced into JDBC in Java 1.4). Since XML is already correctly classified and XML is in JDBC since 1.6, the transition should be justifiable.