PostgreSQL 读取表主键和唯一键的SQL

-- 获取PostgreSQL表的主键和唯一键
SELECT t.table_schema,
       t.table_name,
       a.attname AS column_name,
       c.constraint_type
FROM information_schema.table_constraints c
JOIN information_schema.key_column_usage a ON a.constraint_name = c.constraint_name
                                           AND a.table_schema = c.table_schema
                                           AND a.table_name = c.table_name
JOIN information_schema.tables t ON t.table_schema = c.table_schema
                                 AND t.table_name = c.table_name
WHERE c.constraint_type IN ('PRIMARY KEY', 'UNIQUE')
  AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY t.table_schema, t.table_name, a.ordinal_position;

这段SQL查询会返回数据库中每个表的主键和唯一键，以及它们对应的列名。它排除了系统表，只包含用户定义的表，并按表的schema和名称对结果进行排序。