postgresql查询表的主键

 作者：瀚高PG实验室 （Highgo PG Lab）

--查询主键名称

SELECT

    pg_constraint.conname AS pk_name

FROM

    pg_constraint

INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid

WHERE

    pg_class.relname = ' table_name'

AND pg_constraint.contype = 'p';

--查询主键的详细信息

SELECT

    pg_constraint.conname AS pk_name,

    pg_attribute.attname AS colname,

    pg_type.typname AS typename

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';