Re: getTypeInfo

On 14 December 2016 at 11:27, Jan Motl <yzan(at)volny(dot)cz> wrote:

> 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.
>

Curious: Why do you care about the order ?

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com