本文共 2915 字，大约阅读时间需要 9 分钟。

PostgreSQL 查询库结构及表属性

作为一名技术人员，我常需要对数据库进行深入的查询和分析，以便更好地了解数据库结构和表属性。PostgreSQL 是一个强大的开源数据库管理系统，它提供了丰富的元数据视图和系统表，帮助用户理解数据库的结构和内容。本文将介绍如何查询库结构（用户、模式、表、序列、视图、触发器、物化视图）以及表属性（字段、主键、外键、索引、唯一约束）的相关信息。

库结构（用户、表、序列、视图、触发器、物化视图）

一、用户 查询数据库中所有用户可以通过以下命令进行：

select * from pg_user;

collective_technical_wiki

二、模式 查询数据库中的所有模式可以使用以下命令：

SELECT schema_name FROM information_schema.schemata 
WHERE schema_name NOT IN ('pg_toast','pg_temp_1','pg_toast_temp_1','pg_catalog','information_schema');

此外，可以通过show server_encoding查看数据库的字符编码设置：

show server_encoding;

三、表 根据模式名查询所有表：

SELECT schemaname,tablename FROM pg_tables 
WHERE schemaname = '模式名';

four_colorful_horses

四、序列 查询特定模式下的所有序列：

SELECT sequencename FROM pg_sequences 
WHERE schemaname = '模式名';

五、视图 查询特定模式下的所有视图：

SELECT viewname FROM pg_views 
WHERE schemaname = '模式名';

六、触发器 查询特定模式下的所有触发器：

SELECT TRIGGER_NAME,event_object_table,action_statement 
FROM information_schema.TRIGGERS 
WHERE TRIGGER_SCHEMA = '模式名';

七、物化视图 查询特定模式下的所有物化视图：

SELECT matviewname FROM pg_matviews 
WHERE schemaname = '模式名';

表属性（字段、主键、外键、索引、唯一约束）

一、字段 查询特定表的列属性：

-- 查询字段属性
select ordinal_position column_id,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH data_length,  
IS_NULLABLE nullable,null pk_column 
from information_schema.columns 
where table_schema='模式名' and table_name='表名';

此外，查询字段注释：

SELECT c.relname as tablename ,a.attname as cols,col_description(a.attrelid,a.attnum)  
FROM pg_class as c,pg_attribute as a ,pg_tables as b   
where a.attrelid = c.oid and a.attnum>0 and c.relname=b.tablename   
and b.schemaname='模式名' and c.relname = '表名';

二、主键 查询特定表的主键：

SELECT distinct kcu.column_name FROM information_schema.table_constraints AS tc   
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name  
WHERE constraint_type = 'PRIMARY KEY'   
AND tc.table_name = '表名' and tc.constraint_schema='模式名';

three_d_cubes

三、外键 查询特定表的外键及其外键关联信息：

SELECT  tc.constraint_name, tc.table_name, kcu.column_name,   
ccu.table_name AS foreign_table_name,ccu.column_name AS foreign_column_name  
FROM  information_schema.table_constraints AS tc   
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name   
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name  
WHERE constraint_type = 'FOREIGN KEY'   
AND tc.table_name = '表名' and tc.constraint_schema='模式名';

四、索引 查询表的索引：

select indexname,indexdef from pg_indexes   
where tablename='表名' and schemaname = '模式名'   
and indexname not in(SELECT distinct constraint_name FROM information_schema.table_constraints   
WHERE table_name = '表名' and constraint_schema='模式名');

五、唯一约束 查询特定表的唯一约束：

SELECT distinct tc.constraint_name,kcu.column_name FROM information_schema.table_constraints AS tc     
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name   
WHERE constraint_type = 'UNIQUE'   
AND tc.table_name = '表名' and tc.constraint_schema='模式名';