PostgreSQL 数据库对象管理

1. 数据库与数据库模式

• 核心概念：数据库（Database）是存储数据的容器；数据库模式（Schema）是数据库内的命名空间，用于隔离表、视图等不同对象。

2. 表的创建与管理

（1）char 与 varchar 区别
类型 特点 适用场景 
char(n) 固定长度，不足补空格，查询速度快 长度固定的字段（如性别、编号） 
varchar(n) 可变长度，仅存储实际字符+1字节长度标识 长度不固定的字段（如姓名、描述） 

（2）修改数据表（ALTER TABLE 语法）

• 新增字段：ALTER TABLE xxx ADD gender varchar(1) DEFAULT 'M';

• 修改字段类型：ALTER TABLE xxx ALTER gender TYPE varchar(10);

• 删除字段：ALTER TABLE xxx DROP COLUMN gender;

（3）约束类型
约束类型 作用 示例代码 
外键约束 关联父表字段，保证数据一致性 ALTER TABLE 子表 ADD CONSTRAINT fk_xxx FOREIGN KEY (父表字段) REFERENCES 父表(字段); 
非空约束 字段不可为空 CREATE TABLE test (id int NOT NULL); 
检查约束 限制字段值满足指定条件 CREATE TABLE testcheck (tid int PRIMARY KEY, salary float NOT NULL, CHECK (salary>0 AND salary<10000)); 
主键约束 唯一标识记录，非空且唯一 CREATE TABLE test (id int PRIMARY KEY); 
唯一约束 字段值唯一，可空 CREATE TABLE test (name varchar(10) UNIQUE); 

外键约束关键疑问解答

• 关联规则：子表外键值必须是父表对应字段已存在的值（或为 NULL，若允许）；父表不会自动创建子表外键值，需手动维护或通过级联操作管理。

• 级联操作语法（解决更新/删除关联数据问题）：

◦ 级联更新：FOREIGN KEY (父表字段) REFERENCES 父表(字段) ON UPDATE CASCADE;

◦ 级联删除：FOREIGN KEY (父表字段) REFERENCES 父表(字段) ON DELETE CASCADE;

• 注意：无级联时，需先更新/删除子表关联数据，再操作父表。

3. 数据库统计信息

• 核心系统表：

◦ pg_class：存储数据表、索引的行数、数据块数等基础统计信息。

◦ pg_statistic：存储 ANALYZE 命令生成的统计数据，供查询计划器优化执行计划。

• 实际使用：通过 pg_stat 系列视图（而非直接查询系统表）获取可读性更强的统计信息。

4. 索引与视图

（1）索引

• 作用：加速查询效率，尤其适用于大数据量、频繁查询的字段。

• 常用操作：CREATE INDEX idx_xxx ON 表名(字段名);（可按需选择 B-tree、GIN 等索引类型）。

（2）视图（简化复杂查询）

• 普通视图：CREATE OR REPLACE VIEW 视图名 AS SELECT 字段 FROM 表名 WHERE 条件;

• 视图 DML 操作：支持 INSERT/UPDATE/DELETE，需满足底层表权限和数据完整性约束。

• WITH CHECK OPTION：限制通过视图修改的数据必须满足视图定义的 WHERE 条件（避免数据脱离视图可见范围）。

• 临时视图：仅当前会话有效，会话结束后自动删除：CREATE OR REPLACE TEMPORARY VIEW tempview AS SELECT * FROM xxx;

5. 物化视图

• 特点：物理存储查询结果（非内存），后续查询直接读取存储结果，大幅提升效率（适用于静态或低频更新数据）。

• 核心操作：

1. 创建：CREATE MATERIALIZED VIEW mymatview AS SELECT 字段 FROM 表名;

2. 刷新（同步底层表数据）：

◦ 全量刷新（锁表）：REFRESH MATERIALIZED VIEW mymatview;

◦ 并发刷新（不锁表，需先创建唯一索引）：REFRESH MATERIALIZED VIEW CONCURRENTLY mymatview;

3. 创建唯一索引（支持并发刷新）：CREATE UNIQUE INDEX mymatview_unique ON mymatview(ename);

4. 删除：DROP MATERIALIZED VIEW mymatview;

6. 序列

• 作用：生成自增序列值（常用于主键字段自动赋值）。

• 基础语法：

◦ 创建：CREATE SEQUENCE 序列名 START WITH 1 INCREMENT BY 1;

◦ 调用：NEXTVAL('序列名')（获取下一个序列值）、CURRVAL('序列名')（获取当前序列值）。

◦ 关联表字段：CREATE TABLE test (id int DEFAULT NEXTVAL('序列名') PRIMARY KEY);