【PostgreSQL】【第3章】PostgreSQL的对象操作

2025/4/3 5:43:20 来源：https://blog.csdn.net/cherry5230/article/details/146841000 浏览: 次关键词：【PostgreSQL】【第3章】PostgreSQL的对象操作

1. 表约束

1.1 常见约束类型

主键约束 (PRIMARY KEY)
用于唯一标识表中的每条记录。语法示例：

CREATE TABLE test (ID BIGSERIAL PRIMARY KEY,name VARCHAR(64) NOT NULL
);

非空约束 (NOT NULL)
确保字段值不为空：

CREATE TABLE test (name VARCHAR(64) NOT NULL
);

唯一约束 (UNIQUE)
确保字段值唯一：

CREATE TABLE test (id_card VARCHAR(18) UNIQUE
);

检查约束 (CHECK)
自定义条件验证数据：

CREATE TABLE product (price NUMERIC CHECK(price > 0),discount_price NUMERIC CHECK(discount_price <= price)
);

1.2 外键约束的争议

外键的缺点
可能导致级联操作风险（如误删数据）和维护复杂度高，不推荐使用。

2. 表默认值

2.1 默认值设置

常用场景：自动填充创建时间、更新时间等字段：

CREATE TABLE user (create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. 触发器

3.1 触发器实现级联删除

步骤：

创建学生表和分数表：

CREATE TABLE student (ID INT, name VARCHAR(64));
CREATE TABLE score (student_id INT, math NUMERIC);

编写触发器函数：

CREATE FUNCTION delete_score() RETURNS TRIGGER AS $$
BEGINDELETE FROM score WHERE student_id = OLD.ID;RETURN OLD;
END;
$$ LANGUAGE plpgsql;

绑定触发器：

CREATE TRIGGER trigger_delete_student 
AFTER DELETE ON student 
FOR EACH ROW EXECUTE FUNCTION delete_score();

4. 表空间管理

4.1 表空间的作用

核心功能：指定表的物理存储路径。

创建表空间：

CREATE TABLESPACE ts_test LOCATION '/path/to/directory';

查询表存储路径：

SELECT pg_relation_filepath('student');

4.2 表空间存储问题

软链接机制：默认路径和指定路径会同时存在符号链接，避免重复存储。

5. 视图

5.1 视图的创建与限制

简单视图（单表查询）：

CREATE VIEW student_view AS 
SELECT ID, name FROM student;

复杂视图（多表关联）：

CREATE VIEW student_score AS
SELECT s.name, sc.math 
FROM student s 
JOIN score sc ON s.ID = sc.student_id;

限制：复杂视图不支持写操作。

6. 索引应用

6.1 索引类型与性能对比

B-Tree 索引（默认）：
```
CREATE INDEX idx_name ON test (name);
```

GIN 索引（适用于数组字段）：

CREATE INDEX idx_phones ON user USING GIN (phones);

6.2 性能测试

无索引查询：全表扫描 (Seq Scan)，耗时约 0.5 秒。
有索引查询：索引扫描 (Index Scan)，耗时约 0.05 秒。

7. 物化视图

7.1 物化视图特性

与普通视图的区别：持久化存储数据，查询效率更高。

创建示例：

CREATE MATERIALIZED VIEW mv_product AS 
SELECT ID, name FROM product;

7.2 数据同步方式

全量更新：
```
REFRESH MATERIALIZED VIEW mv_product;
```

增量更新（需唯一索引）：

CREATE UNIQUE INDEX idx_mv_id ON mv_product (ID);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_product;

7.3 更新机制

版本控制：通过 xmin 和 xmax 字段追踪数据版本变化。

关键术语：

约束 (Constraints)：用于保证数据完整性的规则。
触发器 (Trigger)：事件驱动的存储过程。
物化视图 (Materialized View)：缓存复杂查询结果的物理表。

操作提示：

索引优化需根据查询模式选择合适类型。
物化视图的更新频率需平衡性能与数据实时性需求。