POSTGRESQL-JDBC suspected BUG: The table name and column name of the partial interface parameter must all lowercase

After the project was ported from Oracle to PostgreSQL (version 9.4), the days went down again, tracked and positioned to determine that the call to the Pgdatabasemetadata.getprimarykeys () interface returned an empty set.

It is well known that in most cases SQL statements are not sensitive to table names and column names (as far as I can tell, the MySQL default on Linux platforms is case-sensitive for table names, which is an exception). corresponding, the database JDBC should also be insensitive to case, but the reality is: PostgreSQL's JDBC part of the interface only recognize all lowercase table names, column names, and all uppercase, mixed case is not supported.

PostgreSQL stores all lowercase table names and column names in its data dictionary, but the JDBC interface does not convert the case when it is processed. Here is the SQL code for querying the primary key of a table by referencing the source code of the POSTGRESQL-JDBC:

 
 
select pg_attribute.attname as colname,pg_type.typname as typename,pg_constraint.conname as pk_name
from pg_constraint  
inner join pg_class 
  on pg_constraint.conrelid = pg_class.oid 
inner join pg_attribute 
  on pg_attribute.attrelid = pg_class.oid 
    and  pg_attribute.attnum = pg_constraint.conkey[1]
inner join pg_type 
  on pg_type.oid = pg_attribute.atttypid
where pg_class.relname = ‘table_name‘ 
  and pg_constraint.contype=‘p‘;

When the table_name is in full lowercase, the correct results are obtained, and all other results are empty.

Perhaps PostgreSQL is deliberately for some reason, but I tend to think this is a bug. After all, the JDBC of other mainstream databases can handle the situation correctly.

After simple testing, the interface involved in this project includes:

// Get the table primary key
public ResultSet getPrimaryKeys (String catalog, String schema, String table) throws SQLException;

// Get table foreign key
public ResultSet getImportedKeys (String catalog, String schema, String table) throws SQLException;

// Get column names
public String getColumnName (int column); 

The affected interface should be a lot more, and probably more than just pgdatabasemetadata a class; In addition, the schema name, user name, database name, and so on, may also be affected.

However, due to the urgency of the project time is not verified, everyone in the use of attention to this problem, in the application to include the case of the code conversion.

Postgresql-jdbc suspected BUG: The table name and column name of the partial interface parameter must all lowercase