Identity columns don't have any dedicated data types in the SQL Standard and it the most of database systems. When you declare an identity column you must specify both data type (BIGINT
, for example) and identity clause (GENERATED BY DEFAULT AS IDENTITY
Few database systems have special data types with implicit identity characteristics due to historic reasons. PostgreSQL has serial data types (smallserial = serial2, serial = serial4, bigserial = serial8). Normally identity columns should be used in modern versions of PostgreSQL instead of these legacy types, but at least JDBC drivers have something to report here. PgJDBC reports only smallserial, serial, and bigserial data types in getTypeInfo() with TRUE in AUTO_INCREMENT column.
H2 doesn't have special types (compatibility modes have some) and returns FALSE for all data types.
HSQLDB doesn't have special types, but it returns TRUE for TYNYINT, SMALLINT, INTGER, BIGINT, DECIMAL and NUMERIC. These data types may be used for identity columns, but an additional clause must be specified in their definitions.
Derby doesn't have special types, it returns TRUE for SMALLINT, INTEGER, and BIGINT, so situation is the same. It also returns NULL for LOB data types, it looks like a bug.
It means some drivers (PgJDBC, H2) assume that TRUE in this column indicates a special data type with implicit identity characteristics, some other drivers (HSQLDB, Derby) assume that TRUE indicates a data type that may be used for an identity column with additional identity generation clauses.
Why this deviation exists? In the SQL Standard, INFORMATION_SCHEMA is described in a separate book with more than 300 pages and we usually can say where it was implemented correctly and where it isn't correct. But JDBC metadata is only described in its own Javadoc and these descriptions are too brief and obscure. In many cases it isn't possible to determine what they exactly mean, so developers of JDBC drivers need to guess or they can try to copy behavior of some other driver.
I think we can change our implementation to return TRUE for TINYINT, SMALLINT, INTEGER, BIGINT, and NUMERIC data types.