前面的表主要使用自增 bigint 作为主键。PostgreSQL 也原生支持 uuid 类型,并可以通过 gen_random_uuid() 生成随机的 UUIDv4:
bigint
uuid
gen_random_uuid()
1CREATE TABLE public_links ( 2 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), 3 task_id bigint NOT NULL, 4 expires_at timestamptz, 5 created_at timestamptz NOT NULL DEFAULT now(), 6 CONSTRAINT public_links_task_fkey 7 FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE 8);
UUID 适合对外公开的对象编号、需要由多个服务独立生成标识符的对象,以及不希望暴露连续编号的接口。
不过,UUID 首先是标识符,并不会自动带来授权能力。若一个分享链接无需登录、只凭 URL 就能访问数据,那么其中的随机值实际上承担了持有者令牌的作用。此时除了随机性,还要考虑有效期、撤销机制、泄露风险和服务端授权检查,不能只因为地址难以猜测就认为它足够安全。
也不必为了使用 PostgreSQL 就把所有主键改成 UUID。内部高频关联表继续使用紧凑的整数主键完全合理;同一张表还可以同时保留内部整数主键和对外 UUID,只要明确二者的用途:
1BEGIN; 2 3ALTER TABLE projects 4ADD COLUMN public_id uuid NOT NULL DEFAULT gen_random_uuid(); 5 6ALTER TABLE projects 7ADD CONSTRAINT projects_public_id_key UNIQUE (public_id); 8 9COMMIT;
对已有数据的表执行这类修改时,PostgreSQL 需要为旧行生成不同的 UUID,并建立唯一索引。数据量较大时,这可能带来较长的锁等待和 I/O 压力,生产环境通常要先评估表规模,再决定是否分阶段增加列、分批回填并最后添加约束。
PostgreSQL 的 RETURNING 能让 INSERT、UPDATE、DELETE 和 MERGE 直接返回受影响的行,应用不必为了取得数据库生成的值再查询一次。
RETURNING
INSERT
UPDATE
DELETE
MERGE
为了避免示例依赖某个固定的自增 ID,先在 psql 中取一条现有任务,并把结果保存为变量:
psql
1SELECT id AS task_id, project_id 2FROM tasks 3WHERE archived_at IS NULL 4ORDER BY id 5LIMIT 1 6\gset
\gset 是 psql 客户端命令,不是 SQL。后面的 :task_id 和 :project_id 会由 psql 替换为查询结果;应用代码则应通过数据库驱动的参数绑定传值。
\gset
:task_id
:project_id
下面把任务优先级改为 2,并同时返回更新前后的值:
2
1UPDATE tasks 2SET priority = 2, updated_at = now() 3WHERE id = :task_id 4RETURNING 5 id, 6 OLD.priority AS old_priority, 7 NEW.priority AS new_priority, 8 NEW.updated_at;
OLD 和 NEW 是 PostgreSQL 18 为 RETURNING 增加的写法,分别表示修改前和修改后的行。旧版本可以直接返回修改后的列,但不能在这里使用 OLD.column 和 NEW.column。如果 WHERE 没有匹配任务,语句会正常完成并返回 0 行,应用可以据此区分更新成功和资源不存在。
OLD
NEW
OLD.column
NEW.column
WHERE
这里没有直接修改 status,因为前文已经约定:任务状态变化还要在同一个事务中写入 task_status_history。RETURNING 只负责返回行,不能替代这条业务完整性规则。
status
task_status_history
ON CONFLICT 主要用来处理唯一索引或唯一约束引起的冲突。例如,tasks 表已有 tasks_project_number_key 唯一约束,可以把同一项目下的 task_number = 1000 当作本次导入的幂等键:
ON CONFLICT
tasks
tasks_project_number_key
task_number = 1000
01INSERT INTO tasks (project_id, task_number, title, description) 02VALUES ( 03 :project_id, 04 1000, 05 '整理 PostgreSQL 笔记', 06 '由外部导入任务同步' 07) 08ON CONFLICT ON CONSTRAINT tasks_project_number_key 09DO UPDATE SET 10 title = EXCLUDED.title, 11 description = EXCLUDED.description, 12 updated_at = now() 13RETURNING id, project_id, task_number, title, updated_at;
EXCLUDED 表示本次原本准备插入的行。没有冲突时 PostgreSQL 执行插入;发生指定的唯一冲突时,它原子地执行更新。其他约束错误仍会正常报错,不会被 ON CONFLICT 吞掉。
EXCLUDED
不指定冲突目标的 DO NOTHING 还可以忽略排他约束冲突,但排他约束不能作为 DO UPDATE 的仲裁条件。需要更新时,应明确选择一个非延迟的唯一约束或唯一索引。
DO NOTHING
DO UPDATE
如果只需要确保标签存在,而不希望为了取得 ID 产生一次无意义更新,可以先尝试插入:
1INSERT INTO tags (name) 2VALUES ('PostgreSQL') 3ON CONFLICT (name) DO NOTHING 4RETURNING id, name;
插入成功时,RETURNING 会返回新标签;发生冲突并执行 DO NOTHING 时,它返回 0 行,应用再执行一次参数化 SELECT 读取现有标签:
SELECT
1SELECT id, name 2FROM tags 3WHERE name = 'PostgreSQL';
在默认的 READ COMMITTED 隔离级别下,后面的独立 SELECT 会取得新的语句快照。不要为了追求“单条 SQL”就随意把插入和回查压进一个 CTE:并发事务刚提交冲突行时,同一条语句的快照未必能看见那一行。若冲突行随后又被另一个事务删除,应用还需要根据结果决定是否重试。
READ COMMITTED
真实应用应把标签名作为参数传入。这里使用固定值只是为了展示 SQL 结构。
此段落已有评论,按 Enter 查看评论及回复列表。
连接 GitHub 后查看和发表评论,也可在 GitHub 查看公开讨论。
1. UUID
前面的表主要使用自增
bigint作为主键。PostgreSQL 也原生支持uuid类型,并可以通过gen_random_uuid()生成随机的 UUIDv4:UUID 适合对外公开的对象编号、需要由多个服务独立生成标识符的对象,以及不希望暴露连续编号的接口。
不过,UUID 首先是标识符,并不会自动带来授权能力。若一个分享链接无需登录、只凭 URL 就能访问数据,那么其中的随机值实际上承担了持有者令牌的作用。此时除了随机性,还要考虑有效期、撤销机制、泄露风险和服务端授权检查,不能只因为地址难以猜测就认为它足够安全。
也不必为了使用 PostgreSQL 就把所有主键改成 UUID。内部高频关联表继续使用紧凑的整数主键完全合理;同一张表还可以同时保留内部整数主键和对外 UUID,只要明确二者的用途:
对已有数据的表执行这类修改时,PostgreSQL 需要为旧行生成不同的 UUID,并建立唯一索引。数据量较大时,这可能带来较长的锁等待和 I/O 压力,生产环境通常要先评估表规模,再决定是否分阶段增加列、分批回填并最后添加约束。
2. 写入增强
PostgreSQL 的
RETURNING能让INSERT、UPDATE、DELETE和MERGE直接返回受影响的行,应用不必为了取得数据库生成的值再查询一次。为了避免示例依赖某个固定的自增 ID,先在
psql中取一条现有任务,并把结果保存为变量:\gset是psql客户端命令,不是 SQL。后面的:task_id和:project_id会由psql替换为查询结果;应用代码则应通过数据库驱动的参数绑定传值。下面把任务优先级改为
2,并同时返回更新前后的值:OLD和NEW是 PostgreSQL 18 为RETURNING增加的写法,分别表示修改前和修改后的行。旧版本可以直接返回修改后的列,但不能在这里使用OLD.column和NEW.column。如果WHERE没有匹配任务,语句会正常完成并返回 0 行,应用可以据此区分更新成功和资源不存在。这里没有直接修改
status,因为前文已经约定:任务状态变化还要在同一个事务中写入task_status_history。RETURNING只负责返回行,不能替代这条业务完整性规则。ON CONFLICT主要用来处理唯一索引或唯一约束引起的冲突。例如,tasks表已有tasks_project_number_key唯一约束,可以把同一项目下的task_number = 1000当作本次导入的幂等键:EXCLUDED表示本次原本准备插入的行。没有冲突时 PostgreSQL 执行插入;发生指定的唯一冲突时,它原子地执行更新。其他约束错误仍会正常报错,不会被ON CONFLICT吞掉。不指定冲突目标的
DO NOTHING还可以忽略排他约束冲突,但排他约束不能作为DO UPDATE的仲裁条件。需要更新时,应明确选择一个非延迟的唯一约束或唯一索引。如果只需要确保标签存在,而不希望为了取得 ID 产生一次无意义更新,可以先尝试插入:
插入成功时,
RETURNING会返回新标签;发生冲突并执行DO NOTHING时,它返回 0 行,应用再执行一次参数化SELECT读取现有标签:在默认的
READ COMMITTED隔离级别下,后面的独立SELECT会取得新的语句快照。不要为了追求“单条 SQL”就随意把插入和回查压进一个 CTE:并发事务刚提交冲突行时,同一条语句的快照未必能看见那一行。若冲突行随后又被另一个事务删除,应用还需要根据结果决定是否重试。真实应用应把标签名作为参数传入。这里使用固定值只是为了展示 SQL 结构。