AnolisOS 8.8 PostgreSQL 查看表的主键

关系型数据库设计表结构的基础原则之一就是表结构需要有主键。主键的设计不仅决定了行数据的唯一性，在数据的物理存储上也起到了至关重要的作用。

在 PostgreSQL 数据库中有一个非常重要的技术实现，发布订阅功能。其要求所涉及的表必须要有主键，否则当你在执行对表数据的增删改查的时候会提示你：

ERROR: cannot delete from table "XX" because it does not have a replica identity and publishes deletes

所以，了解系统涉及的表是否有主键以及哪个字段被设置为主键就体现的至关重要。下面我给出了在 PostgreSQL 数据库中如何查看表是否有主键以及主键的字段是啥的检索语句。

该查询涉及到的系统表或者视图有如下几个：
pg_class、pg_namespace、pg_constraint、pg_attribute、pg_type。

pg_class 
pg_class 记录了表和几乎所有具有列或者像表的东西。这包括索引、序列、视图、物化视图、组合类型和 TOAST 表。
oid 行标识符
relname 表、索引、视图等的名字
relnamespace pg_namespace.oid 包含该关系的名字空间的 OID
reltype pg_type.oid 可能存在的表行类型所对应数据类型的 OID，对索引为 0，索引没有 pg_type 项
relowner pg_authid.oid 关系的拥有者
relam pg_am.oid 如果这是一个表或者索引，表示索引使用的访问方法，堆、B 树、哈希等
relfilenode 该关系的磁盘文件的名字，0 表示这是一个映射关系，其磁盘文件名取决于低层状态
reltablespace pg_tablespace.oid 该关系所存储的表空间。如果为 0 使用数据库的默认表空间
relhasindex 如果这是一个表并且其上建有或最近建有索引则为真
relkind r 普通表 i 索引 S 序列 t TOAST 表 v 视图 m 物化视图 c 组合类型 f 外部表 p 分区表 I 分区索引

pg_namespace
pg_namespace 存储名字空间。名字空间是 SQL 模式之下的结构：每个名字空间拥有一个独立的表、类型等的集合，且其中没有名字冲突。
oid 行标识符
nspname 名字空间的名字
nspowner pg_authid.oid 名字空间的拥有者

pg_constraint
pg_constraint 存储表上的检查、主键、唯一、外键和排他约束。非空约束不在这里，而是在 pg_attribute 目录中表示。
oid 行标识符
conname 约束名字
connamespace pg_namespace.oid 包含此约束的名字空间的 OID
contype c 检查约束 f 外键约束 p 主键约束 u 唯一约束 t 约束触发器 x 排他约束

pg_attribute
pg_attribute 存储有关表列的信息。数据库中的每一个表的每一个列都恰好在 pg_attribute 中有一行。
attrelid pg_class.oid 列所属的表
attname 列名
atttypid pg_type.oid 列的数据类型
attnum 列的编号。一般列从 1 开始向上编号。系统列如 ctid 则拥有任意负值编号。
attnotnull 这表示一个非空约束。

pg_type
pg_type 存储有关数据类型的信息
oid 行标识符
typname 数据类型的名字
typnamespace 包含此类型的名字空间的 OID
typowner pg_authid.oid 类型的拥有者

查看表的主键 SQL：
WITH tmp_tab AS (
SELECT n.nspname AS schemaname,
       c.oid AS reloid,
       c.relname,
       pg_catalog.pg_get_userbyid(c.relowner) AS ownername
FROM pg_catalog.pg_class AS c
LEFT JOIN pg_catalog.pg_namespace AS n ON c.relnamespace = n.oid 
WHERE 1=1 --r 普通表 i 索引 S 序列 t TOAST 表 v 视图 m 物化视图 c 组合类型 f 外部表 p 分区表 I 分区索引
AND c.relkind IN ('r','p','m','f','')
AND n.nspname <> 'pg_catalog' 
AND n.nspname <> 'information_schema' 
AND n.nspname !~ '^pg_toast' 
AND pg_catalog.pg_table_is_visible(c.oid)
)
SELECT current_database() AS databasename,
       tab.schemaname,
       tab.reloid,
       tab.relname,
       tab.ownername,
       pco.conname AS pk_name,
       pa.attname AS col_name,
       pt.typname AS col_type
FROM tmp_tab AS tab
LEFT JOIN pg_constraint AS pco ON pco.conrelid = tab.reloid AND pco.contype = 'p' 
LEFT JOIN pg_attribute AS pa ON pa.attrelid = tab.reloid AND pa.attnum = pco.conkey [1]
LEFT JOIN pg_type AS pt ON pt.oid = pa.atttypid
ORDER BY databasename,schemaname,relname,col_name;

查找出当前数据库下没有主键的表
SELECT table_schema,
       table_name
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_type = 'BASE TABLE'
AND table_name NOT IN (
    SELECT table_name
    FROM information_schema.table_constraints
    WHERE constraint_schema = 'public' 
    AND table_schema = 'public'
    AND constraint_type = 'PRIMARY KEY' );