Navigate
X

PostgreSQLでのテーブルのプライマリーキー取得クエリ

下記のクエリはユーザ権限が、DBのマスター権限が無いと取得できません

SELECT t.table_catalog,
t.table_schema,
t.table_name,
kcu.constraint_name,
kcu.column_name,
kcu.ordinal_position
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON tc.table_catalog = t.table_catalog
AND tc.table_schema = t.table_schema
AND tc.table_name = t.table_name
AND tc.constraint_type = ‘PRIMARY KEY’
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON kcu.table_catalog = tc.table_catalog
AND kcu.table_schema = tc.table_schema
AND kcu.table_name = tc.table_name
AND kcu.constraint_name = tc.constraint_name
WHERE t.table_schema NOT IN (‘pg_catalog’, ‘information_schema’)
and t.table_name = ‘<テーブル名>’
ORDER BY t.table_catalog,
t.table_schema,
t.table_name,
kcu.constraint_name,
kcu.ordinal_position;

参考URL

http://www.postgresql.org/message-id/4E94DB9F.7090607@gmail.com

健巳: