PostgreSQL表操作

1 约束

如果有SQL相关的基础，那么表的创建操作大家应该都会，关键是构建表时要指定上一些约束。

PostgreSQL的约束主要有 6 种，包括检查约束、非空约束、唯一约束、主键、外键、排他约束。

1_检查约束

一个检查约束是最普通的约束类型，它允许我们指定一个特定列中的值必须要满足一个布尔表达式。

如下：

-- 检查约束
-- 价格的表，price，discount_price
drop table test;
create table test(id bigserial primary key,name varchar(32) not null,price numeric check(price > 0),discount_price numeric check(discount_price > 0),-- 甚至可以单独写一列check(price >= discount_price)
);
insert into test (name,price,discount_price) values ('粽子',122,12);

2_非空约束

一个非空约束仅仅指定一个列中不会有空值，如下示例

-- 非空约束
drop table test;
create table test(id bigserial primary key ,name varchar(32) not null
);

3_唯一约束

唯一约束保证\在一列中或者一组列中保存的数据在表中所有行间是唯一的。

drop table test;
create table test(id bigserial primary key ,name varchar(32) not null,id_card varchar(32) unique,UNIQUE (id, name)
);
insert into test (name,id_card) values ('张三','333333333333333333');
insert into test (name,id_card) values ('李四','333333333333333333');
insert into test (name,id_card) values (NULL,'433333333333333333');

通常情况下，如果表中有多行，其中包含约束中包含的所有列的值相等，则违反了唯一约束。

默认情况下，在此比较中，两个空值不被视为相等。

这意味着即使存在唯一约束，也可以存储包含至少一个受约束列中的空值的重复行。

可以通过添加子句 NULLS NOT DISTINCT 来更改此行为，例如：

CREATE TABLE products (product_no integer UNIQUE NULLS NOT DISTINCT,-- UNIQUE NULLS NOT DISTINCT (product_no),name text,price numeric
);

4 主键

一个主键约束表示可以用作表中行的唯一标识符的一个列或者一组列。

CREATE TABLE products (product_no integer UNIQUE NOT NULL,name text,price numeric
)

这要求那些值都是唯一的并且非空。

增加一个主键将自动在主键中列出的列或列组上创建一个唯一 B-tree 索引。并且会强制这些列被标记为 NOT NULL。

主键也可以包含多于一个列，其语法和唯一约束相似：

CREATE TABLE example (a integer,b integer,c integer,PRIMARY KEY (a, c)
);

一个表最多只能有一个主键（可以有任意数量的唯一和非空约束，它们可以达到和主键几乎一样的功能，但只能有一个被标识为主键）。

关系数据库理论要求每一个表都要有一个主键。

但PostgreSQL中并未强制要求这一点，但是最好能够遵循它。

5 外键

一个外键约束指定一列（或一组列）中的值必须匹配出现在另一个表中某些行的值。

也就是说这维持了两个关联表之间的引用完整性。

CREATE TABLE child (child_id SERIAL PRIMARY KEY,parent_id INTEGER,-- parent_id INTEGER REFERENCES parent(parent_id)FOREIGN KEY (parent_id) REFERENCES parent(parent_id)
);

扩展

FOREIGN KEY (column_name) REFERENCES target_table(target_column)[ON DELETE action][ON UPDATE action][MATCH match_type][DEFERRABLE | NOT DEFERRABLE]

字段说明：

关键词	含义
FOREIGN KEY	声明外键约束。
REFERENCES	指定被引用的表和列。
ON DELETE	当被引用行被删除时的行为。
ON UPDATE	当被引用行主键被更新时的行为。
MATCH	多列外键匹配策略（SIMPLE、FULL、PARTIAL）。
DEFERRABLE	外键约束是否可以延迟检查。

ON DELETE / ON UPDATE 的动作详解

动作	含义
NO ACTION	默认值。若违反外键约束则抛错。
RESTRICT	类似 NO ACTION，但更早检查，不能推迟。
CASCADE	删除/更新父表数据时，同步影响子表（自动删除或更新）。
SET NULL	删除/更新父表数据时，子表对应外键字段置为 NULL。
SET DEFAULT	设置为默认值（若字段定义了 DEFAULT）。

示例：

FOREIGN KEY (parent_id) REFERENCES parent(parent_id) ON DELETE CASCADE

表示：当 parent 表的某条记录被删除时，child 表中引用它的记录也会被删除。

注：一般请款下都会使用逻辑外键，而这种外键在企业中是被明确要求禁用的。

6 排他约束

排他约束保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回否或空值。

CREATE TABLE circles (c circle,-- 使用 GiST 索引,插入的圆都不能有交集EXCLUDE USING gist (c WITH &&)
);

增加一个排他约束将在约束声明所指定的类型上自动创建索引。

7 默认值

一般公司内，要求表中除了主键和业务字段之外，必须要有5个字段
create_time，create_by，update_time，update_by，is_delete。

CREATE TABLE test (id BIGSERIAL PRIMARY KEY,-- 业务字段（举个例子，可根据需要增加）name TEXT,description TEXT,-- 审计字段created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- 创建时间create_id BIGINT DEFAULT 0,                          -- 创建人ID，默认系统updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- 更新时间，默认与创建时间一致update_id BIGINT DEFAULT 0,                          -- 更新人ID，默认系统is_delete BOOLEAN DEFAULT FALSE                      -- 是否删除，逻辑删除标志
);

2 触发器

触发器 Trigger，是由事件出发的一种存储过程。

当对标进行 insert，update，delete，truncate 操作时，会触发表的 Trigger（看触发器的创建时指定的事件）

构建两张表，学生信息表，学生分数表，填充数据。

create table student(id int,name varchar(32)
);
create table score(id int,student_id int,math_score numeric,english_score numeric,chinese_score numeric
);
insert into student (id,name) values (1,'张三');
insert into student (id,name) values (2,'李四');
insert intoscore
(id,student_id,math_score,english_score,chinese_score)values
(1,1,66,66,66);insert intoscore
(id,student_id,math_score,english_score,chinese_score)values
(2,2,55,55,55);select * from student join score s on student.id = s.student_id;

目标：在删除学生信息的同时，自动删除学生的分数。

为了完成级联删除的操作，需要编写pl/sql（除了 pl/sql ，还支持其他语言）。

先查看一下PGSQL支持的plsql，查看一下PGSQL的plsql语法

[ <<label>> ]
[ DECLAREdeclarations ]
BEGINstatements
END [ label ];

构建一个存储函数，测试一下plsql

-- 优先玩一下plsql
-- $$可以理解为是一种特殊的单引号，避免你在declare，begin，end中使用单引号时，出现问题，
-- 需要在编写后，在$$之后添加上当前内容的语言。
create function test() returns int as $$
declaremoney int := 10;
beginreturn money;
end;
$$ language plpgsql;select test();

在简单了解了一下plpgsql的语法后，编写一个触发器函数。

触发器函数允许使用一些特殊变量

• NEW：数据类型是RECORD；该变量在行级触发器表示即将写入表中的新数据（INSERT 或 UPDATE 时）。在语句级别的触发器以及DELETE操作，这个变量是null。

• OLD：数据类型是RECORD；该变量在行级触发器表示即将被删除或被更新的原始数据。在语句级别的触发器以及INSERT操作，这个变量是null。

构建一个删除学生分数的触发器函数。

-- 构建一个删除学生分数的触发器函数。
create function trigger_function_delete_student_score() returns trigger as $$
begindelete from score where student_id = old.id;return old;
end;
$$ language plpgsql;

开始构建触发器，在学生信息表删除时，执行前面声明的触发器函数

CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }ON table_name[ FROM referenced_table_name ][ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ][ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ][ FOR [ EACH ] { ROW | STATEMENT } ][ WHEN ( condition ) ]EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )where event can be one of:INSERTUPDATE [ OF column_name [, ... ] ]DELETETRUNCATE

当 CONSTRAINT选项被指定，这个命令会创建一个 约束触发器 。这和一个常规触发器相同，不过触发该触发器的时机可以使用SET CONSTRAINTS调整。

约束触发器必须是表上的 AFTER ROW触发器。

它们可以在导致触发器事件的语句末尾被引发或者在包含该语句的事务末尾被引发。在后一种情况中，它们被称作是被 延迟 。

一个待处理的延迟触发器的引发也可以使用 SET CONSTRAINTS立即强制发生。当约束触发器实现的约束被违背时，约束触发器应该抛出一个异常。

描绘一波~~

-- 编写触发器，指定在删除某一行学生信息时，触发当前触发器，执行触发器函数
create trigger trigger_student after delete on student for each row
execute function trigger_function_delete_student_score();
-- 测试效果
select * from student join score s on student.id = s.student_id;
delete from student where id = 1;
select * from score s join student on student.id = s.student_id;

3 表空间

在存储数据时，数据肯定要落到磁盘上，基于构建的tablespace，指定数据存放在磁盘上的物理地址。

如果没有自己设计 tablespace，PGSQL会自动指定一个位置作为默认的存储点。

可以通过一个函数，查看表的物理数据存放在了哪个磁盘路径下。

-- 查询表存储的物理地址
select pg_relation_filepath('student');

如下：

这个位置是在 $PG_DATA 后的存放地址（docker 内）：/var/lib/postgresql/data/pg_data 。

说明：

项	描述
PG_VERSION	一个包含PostgreSQL主版本号的文件
base	包含每个数据库对应的子目录的子目录
current_logfiles	记录当前被日志收集器写入的日志文件的文件
global	包含集簇范围的表的子目录，比如pg_database
pg_commit_ts	包含事务提交时间戳数据的子目录
pg_dynshmem	包含被动态共享内存子系统所使用的文件的子目录
pg_logical	包含用于逻辑复制的状态数据的子目录
pg_multixact	包含多事务（multi-transaction）状态数据的子目录（用于共享的行锁）
pg_notify	包含LISTEN/NOTIFY状态数据的子目录
pg_replslot	包含复制槽数据的子目录
pg_serial	包含已提交的可序列化事务信息的子目录
pg_snapshots	包含导出的快照的子目录
pg_stat	包含用于统计子系统的永久文件的子目录
pg_stat_tmp	包含用于统计信息子系统的临时文件的子目录
pg_subtrans	包含子事务状态数据的子目录
pg_tblspc	包含指向表空间的符号链接的子目录
pg_twophase	包含用于预备事务状态文件的子目录
pg_wal	包含 WAL （预写日志）文件的子目录
pg_xact	包含事务提交状态数据的子目录
postgresql.auto.conf	一个用于存储由ALTER SYSTEM 设置的配置参数的文件
postmaster.opts	一个记录服务器最后一次启动时使用的命令行参数的文件
postmaster.pid	一个锁文件，记录着当前的 postmaster 进程ID（PID）、集簇数据目录路径、postmaster启动时间戳、端口号、Unix域套接字目录路径（Windows上为空）、第一个可用的listen_address（IP地址或者*，或者为空表示不在TCP上监听）以及共享内存段ID（服务器关闭后该文件不存在）

构建表空间，指定数据存放位置：

-- 构建表空间,构建表空间需要用户权限是超级管理员，其次需要指定的目录已经存在
create tablespace tp_test location '/var/lib/postgresql/data/pg_data/tp_test';

构建数据库，以及表，指定到这个表空间中

4 存储结构

所有表和索引的底层数据都保存在一组固定大小的 页面（Page） 中，默认大小为 8KB。

一个页面包含五个部分：

区域名	说明
PageHeaderData	页面头信息（24字节），包含空闲空间位置、校验等元信息
ItemIdData	每行的“索引”，指向行在本页中的偏移和长度（每个4字节）
Free Space	空闲空间，ItemId 从这头部分配，行数据从尾部反向分配
Items	实际存储的表行内容
Special Space	索引用区块，普通表不使用（由 pd_special 标示起始位置）

页面头 PageHeaderData 详细结构：

字段名	类型	描述
pd_lsn	PageXLogRecPtr	最后修改这个页面的 WAL 日志位置
pd_checksum	uint16	页面校验和（若启用了数据校验）
pd_flags	uint16	页面标志位
pd_lower	LocationIndex	ItemIdData 区域的结束位置（从头向后增长）
pd_upper	LocationIndex	Items 数据区的起始位置（从尾向前分配）
pd_special	LocationIndex	Special Space 起始位置（普通表等于页面大小）
pd_pagesize_version	uint16	页面大小 + 页面格式版本
pd_prune_xid	TransactionId	页面上最老未删除记录的 xmax

ItemId 与 Item（实际行）：

• ItemIdData 是表中每行在页面内的“索引”：包含偏移量和长度。
• Item 是实际存储的行数据结构，具体由表的存储类型决定。

PostgreSQL 用 CTID 来标识每一行，它由 (page_number, ItemId index) 构成。

Heap 表中的行结构（Tuple）：

所有普通表使用 HeapTupleHeaderData 结构来组织行，组成部分如下：

字段名	类型	说明
t_xmin	TransactionId	插入该行的事务 ID
t_xmax	TransactionId	删除该行的事务 ID（若未删除为0）
t_cid/t_xvac	CommandId	插入或删除时的命令 ID / vacuum 事务信息
t_ctid	ItemPointerData	指向更新后的下一版本（或自身）
t_infomask2	uint16	属性数量、是否有 NULL 位图等
t_infomask	uint16	多个标志位（是否压缩、是否有 OID、是否 TOAST 等）
t_hoff	uint8	到用户数据部分的偏移，需为 MAXALIGN 的倍数（通常 8字节对齐）

可选字段：

• NULL 位图：存在时位于头部后；1 表示非空，0 表示 NULL。
• OID：若表含有对象 ID，则也放在头部尾部。
• 填充字节：确保 t_hoff 以 8 字节对齐。

用户数据：

• 定长字段直接放入
• 变长字段（attlen=-1）使用 varlena 结构（包括长度、标志位等）

读取字段时的步骤（如 heap_getattr 函数）：

1. 先查 NULL 位图，判断该字段是否为空。
2. 根据字段类型和 pg_attribute.attalign 做对齐。
3. 根据 attlen 读取数据：
   • 定长字段：按字节读取
   • 变长字段：读取 varlena，判断是否压缩或 TOAST 存储

总结：

概念	关键点描述
页面结构（Page）	一页默认 8KB，包含头部、行索引、数据、空闲区等
行索引（ItemId）	记录偏移和长度，用于引用行，不随行移动变化
表行结构（Tuple）	有固定头部、可选 OID/空值位图、数据，需对齐
数据读取	依赖 pg_attribute 和头部标志，可能涉及 TOAST

5 视图

跟 MySQL 没啥区别，把一些复杂的操作封装起来，还可以隐藏一些敏感数据。

视图对于用户来说，就是一张真实的表，可以直接基于视图查询一张或者多张表的信息。

视图对于开发者来说，就是一条SQL语句。

在PGSQL中，简单（单表）的视图是允许写操作的。

但是强烈不推荐对视图进行写操作，虽然PGSQL默认允许（简单的视图）。

写入的时候，其实修改的是表本身

-- 构建一个简单视图
create view vw_score as 
(select id,math_score from score);select * from vw_score;
update vw_score set math_score = 99 where id = 2;

多表视图

-- 复杂视图(两张表关联)
create view vw_student_score as 
(select stu.id as id ,stu.name as name ,score.math_score from student stu,score score where stu.id = score.student_id);select * from vw_student_score;update vw_student_score set math_score =999 where id = 2;

注意：

• 普通视图没有索引，查询时会展开原始 SQL，性能依赖底层表和查询优化器。
• 不能在普通视图上创建索引（只能在物化视图上创建）。
• 视图不是快照，每次查询时都会重新执行其定义的 SQL。
• 修改表结构可能会影响视图，必要时需使用 CREATE OR REPLACE VIEW 更新定义。

6 索引

1 概念

索引是数据库中快速查询数据的方法。

索引能提升查询效率的同时，也会带来一些问题

• 增加了存储空间
• 写操作时，花费的时间比较多

索引可以提升效率，甚至还可以给字段做一些约束

2 索引类型

PostgreSQL 支持以下几种索引：

类型	特点与适用场景
B-tree	默认索引类型，支持等值和范围查询（=, <, <=, >, >=），最常用
Hash	仅支持等值查询（=），性能一般，不支持排序
GiST	通用框架，适合复杂类型（如几何、全文等）的范围与邻近查询
SP-GiST	类似 GiST，支持更多非平衡树结构，如四叉树、K-D树，适合不规则分布的数据
GIN	倒排索引，适用于数组、JSONB、全文搜索等结构，支持包含查询、交集判断等
BRIN	基于块范围的索引，适合非常大的、按列有序的数据表（如时间序列）
Bloom	扩展插件，实现空间占用极低的“模糊索引”，用于多列联合查询优化

3 简单示例

准备大量测试数据，方便查看索引效果

-- 测试索引效果
create table tb_index(id bigserial primary key,name varchar(64),phone varchar(64)[]
);-- 添加300W条数据测试效果
do $$
declarei int := 0;
beginwhile i < 3000000 loopi = i + 1;insert intotb_index(name,phone) values(md5(random()::text || current_timestamp::text)::uuid,array[random()::varchar(64),random()::varchar(64)]);end loop;
end;
$$ language plpgsql;

在没有索引的情况下，先基于name做等值查询，看时间，同时看执行计划

-- c0064192-1836-b019-c649-b368c2be31ca
select * from tb_index where id = 2222222;
select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
explain select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
-- Seq Scan  这个代表全表扫描
-- 时间大致0.3秒左右

在有索引的情况下，再基于name做等值查询，看时间，同时看执行计划

-- name字段构建索引（默认就是b-tree）
create index index_tb_index_name on tb_index(name);
-- 测试效果
select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
explain select * from tb_index where name = 'c0064192-1836-b019-c649-b368c2be31ca';
-- Index Scan 使用索引
-- 0.1s左右

---

测试GIN索引效果

在没有索引的情况下，基于phone字段做包含查询

-- phone：{0.6925242730781953,0.8569644964711074}
select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
explain select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
-- Seq Scan 全表扫描
-- 0.5s左右

给phone字段构建GIN索引，在查询

-- 给phone字符串数组类型字段构建一个GIN索引
create index index_tb_index_phone_gin on tb_index using gin(phone);
-- 查询
select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
explain select * from tb_index where phone @> array['0.6925242730781953'::varchar(64)];
-- Bitmap Index 位图扫描
-- 0.1s以内完成

7 物化视图

普通视图，本质就是一个SQL语句，普通的视图并不会本地磁盘存储任何物理。

每次查询视图都是执行这个SQL。效率有点问题。

物化视图从名字上就可以看出来，必然是要持久化一份数据的。

使用套路和视图基本一致，这样一来查询物化视图，就相当于查询一张单独的表。

相比之前的普通视图，物化视图就不需要每次都查询复杂SQL，每次查询的都是真实的物理存储地址中的一份数据（表）。

物化视图因为会持久化到本地，完全脱离原来的表结构。

而且物化视图是可以单独设置索引等信息来提升物化视图的查询效率。

But，有好处就有坏处，更新时间不太好把控。 如果更新频繁，对数据库压力也不小。 如果更新不频繁，会造成数据存在延迟问题，实时性就不好了。

如果要更新物化视图，可以采用触发器的形式，当原表中的数据被写后，可以通过触发器执行同步物化视图的操作。或者就基于定时任务去完成物化视图的数据同步。

look 一下语法。

-- 构建物化视图
create materialized view mv_test as (select id,name,price from test);
-- 操作物化视图和操作表的方式没啥区别。
select * from mv_test;
-- 操作原表时，对物化视图没任何影响
insert into test values (4,'月饼',50,10);
-- 物化视图的添加操作(不允许写物化视图)，会报错
insert into mv_test values (5,'大阅兵',66);

物化视图如何从原表中进行同步操作。

PostgreSQL中，对物化视图的同步，提供了两种方式，一种是全量更新，另一种是增量更新。

全量更新语法，没什么限制，直接执行，全量更新

-- 查询原来物化视图的数据
select * from mv_test;
-- 全量更新物化视图
refresh materialized view mv_test;
-- 再次查询物化视图的数据
select * from mv_test;

增量更新，增量更新需要一个唯一标识，来判断哪些是增量，同时也会有行数据的版本号约束。

-- 查询原来物化视图的数据
select * from mv_test;
-- 增量更新物化视图，因为物化视图没有唯一索引，无法判断出哪些是增量数据
refresh materialized view concurrently mv_test;
-- 给物化视图添加唯一索引。
create unique index index_mv_test on mv_test(id);
-- 增量更新物化视图
refresh materialized view concurrently mv_test;
-- 再次查询物化视图的数据
select * from mv_test;
-- 增量更新时，即便是修改数据，物化视图的同步，也会根据一个xmin和xmax的字段做正常的数据同步update test set name = '汤圆' where id = 5;
insert into test values (5,'猪头肉',99,40);
select * from test;