getTypeInfo() may not return data in the order specified in Oracle documentation (https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTypeInfo()):

[davecramer]

Retrieves a description of all the data types supported by this database. They are ordered by DATA_TYPE and then by how closely the data type maps to the corresponding JDBC SQL type.
With the following code snippet:
try (Connection connection = dataSource.getConnection(); ResultSet rs = connection.getMetaData().getTypeInfo()) {
while (rs.next()) {
System.out.println(rs.getInt("DATA_TYPE"));
}
} catch (SQLException e) {}
I am getting DATA_TYPE in a non-monotonic order:
-7
-2
1
12
-5
...

Additional metadata:
JDBC driver: PostgreSQL 9.4.1212
Server version: 9.6beta1.0 (9.6beta1.0)

[GuntherRademacher]

Ordering just by numeric type code, as in the proposed fix, is not sufficient. The spec also says

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

With server 10.1 and JDBC 42.1.4, the result contains

DATA_TYPE TYPE_NAME
--------- ---------
       12 name
       12 text
        8 float8
        8 money
       12 varchar

But for being able to find the best match for Types.VARCHAR (12) and Types.DOUBLE (8), they should appear in this order:

DATA_TYPE TYPE_NAME
--------- ---------
        8 float8
        8 money
       12 varchar
       12 text
       12 name

[davecramer]

can you propose a fix to pr#910

[yan-hic]

As a quick fix if you have an app you cannot change the code for, I have forked pgjdbc to assign Types.OTHER for text and name. This effectively forces the app the assign varchar to java.sql.type 12.

Not a fix though, until someone can come up with how to sort TypeInfo properly.

[yan-hic]

@davecramer what about changing the sql in getTypeInfo() to the following:

SELECT t.typname,t.oid 
, case when typname ~ 'varchar|float8' then true else false end as best_match
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) 
WHERE n.nspname  != 'pg_toast'
AND 
(t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
ORDER BY best_match DESC

No harm to have a 3rd column as it is not read.

[davecramer]

@yiga2 agreed, feel like providing us with a PR ?

[davecramer]

fixed in #1506

[yan-hic]

Not sure this is fixed actually. Just tried with 42.3.2 snapshot and DATA_TYPE 12 (mapped from String) still returns name as first match, instead of varchar as per the specs.
The result is that a 3rd party app we use builds DDLs like create table xxx name(10) which is incorrect (and invalid).
Please reopen if confirmed.

[yan-hic]

As mentioned earlier, a not-so-hacky way actually to achieve this is to:

1. map text to a better matching SqlType, namely LONGVARCHAR since it is unlimited. This means in TypeInfoCache, types array:

      {"text", Oid.TEXT, Types.LONGVARCHAR, "java.lang.String", Oid.TEXT_ARRAY},

2. as for name, it should not be listed as a mappable type. As per https://www.postgresql.org/docs/current/datatype-character.html#DATATYPE-CHARACTER-SPECIAL-TABLE, it is not for general use so will never be used in DDLs for example. Besides, the documentation says it's fixed 64 bytes so if to be kept, it should be best mapped to Types.NCHAR to not interfere with Types.CHAR for the same.

[davecramer]

So, finally had a chance to look at this and

1. text is not unlimited.
2. Not sure I agree with name, but I do agree it is out of order. There are many types in there that will not be used by DDL statements.