DBMS independent way to access table schema including primary keys if any

Question

I need to validate table schema using JDBC/Java. In MySQL and H2, I can issue the following query:

SHOW COLUMNS FROM City;

which has the output

+------------+----------+------+-----+---------+----------------+
| COLUMN_NAME| TYPE     | Null | KEY | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

I am only interested in the columns COLUMN_NAME, TYPE and KEY

However, PostgreSQL does not support this query. I tried various combinations based on this post such as the following, but I get weird results.

SELECT c.COLUMN_NAME as COLUMN_NAME, c.DATA_TYPE as TYPE, 
CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRI' ELSE '' END AS KEY
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN (
            SELECT ku.TABLE_CATALOG,ku.TABLE_SCHEMA,ku.TABLE_NAME,ku.COLUMN_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku
                ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
                AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
         )   pk 
ON  c.TABLE_CATALOG = pk.TABLE_CATALOG
            AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
            AND c.TABLE_NAME = pk.TABLE_NAME
            AND c.COLUMN_NAME = pk.COLUMN_NAME
WHERE c.TABLE_NAME = 'City'

Can anyone help with the correct SQL.

EDIT: Problem solved. I was getting no entries for some tables while the correct ones for some. It was a problem with using uppercase in the table name. I used lower case city and it worked fine.

`DatabaseMetaData.getColumns()`: http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getColumns%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String%29 — , Aug 31 '14 at 16:38
The use `getPrimaryKeys()`: http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getPrimaryKeys%28java.lang.String,%20java.lang.String,%20java.lang.String%29 — , Aug 31 '14 at 18:56

score 0 · Answer 1 · answered Sep 01 '14 at 00:59

If using JDBC, you can use JDBC's metadata features.

For a driver-independent option, you can query the information_schema - in this case, you want information_schema.tables, information_schema.columns, information_schema.table_constraints, and information_schema.key_column_usage.

score 0 · Answer 2 · answered Sep 01 '14 at 04:12

0

The code in the question is the correct way to read schema in dbms and driver independent manner. The problem is with the case of the table name. Using upper case characters in the name causes a problem which seems to be a bug. (tested this only in PostgreSQL);

The following SQL works fine, where I changed City to city.

SELECT c.COLUMN_NAME as COLUMN_NAME, c.DATA_TYPE as TYPE, 
CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRI' ELSE '' END AS KEY
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN (
            SELECT ku.TABLE_CATALOG,ku.TABLE_SCHEMA,ku.TABLE_NAME,ku.COLUMN_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku
                ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
                AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
         )   pk 
ON  c.TABLE_CATALOG = pk.TABLE_CATALOG
            AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
            AND c.TABLE_NAME = pk.TABLE_NAME
            AND c.COLUMN_NAME = pk.COLUMN_NAME
WHERE c.TABLE_NAME = 'city'

answered Sep 01 '14 at 04:12

Jus12

17,824
28
99
157

What makes you think it's a bug? That's per SQL standard. `CREATE TABLE City` creates a table named `city` (or `CITY` if you case-fold to upper case instead of PostgreSQL's lower-case folding). If you want a table named `City` you must `CREATE TABLE "City"`, then refer to it that way everywhere. – Craig Ringer Sep 01 '14 at 04:49
@CraigRinger I created the table named `'City'` using single quotes.. Does the double quote make a difference? – Jus12 Sep 01 '14 at 05:55
No you didn't. That's illegal syntax. You can't do that. Try it: `CREATE TABLE 'City' (id integer);` will return `syntax error near unexpected token `('` . Perhaps you mean a quoted string in some client language? What matters is what you send *to PostgreSQL*. – Craig Ringer Sep 01 '14 at 08:48
Actually checking again, I didn't use quotes at all. – Jus12 Sep 01 '14 at 10:44
Exactly - so it got downcased, per the syntax rules. – Craig Ringer Sep 01 '14 at 11:14

DBMS independent way to access table schema including primary keys if any

2 Answers2