1. UUID

前面的表主要使用自增 bigint 作为主键。PostgreSQL 也原生支持 uuid 类型，并可以通过 gen_random_uuid() 生成随机的 UUIDv4：

1
CREATE 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，只要明确二者的用途：

1
BEGIN;
2

3
ALTER TABLE projects
4
ADD COLUMN public_id uuid NOT NULL DEFAULT gen_random_uuid();
5

6
ALTER TABLE projects
7
ADD CONSTRAINT projects_public_id_key UNIQUE (public_id);
8

9
COMMIT;

对已有数据的表执行这类修改时，PostgreSQL 需要为旧行生成不同的 UUID，并建立唯一索引。数据量较大时，这可能带来较长的锁等待和 I/O 压力，生产环境通常要先评估表规模，再决定是否分阶段增加列、分批回填并最后添加约束。

2. 写入增强

PostgreSQL 的 RETURNING 能让 INSERT、UPDATE、DELETE 和 MERGE 直接返回受影响的行，应用不必为了取得数据库生成的值再查询一次。

为了避免示例依赖某个固定的自增 ID，先在 psql 中取一条现有任务，并把结果保存为变量：

1
SELECT id AS task_id, project_id
2
FROM tasks
3
WHERE archived_at IS NULL
4
ORDER BY id
5
LIMIT 1
6
\gset

\gset 是 psql 客户端命令，不是 SQL。后面的 :task_id 和 :project_id 会由 psql 替换为查询结果；应用代码则应通过数据库驱动的参数绑定传值。

下面把任务优先级改为 2，并同时返回更新前后的值：

1
UPDATE tasks
2
SET priority = 2, updated_at = now()
3
WHERE id = :task_id
4
RETURNING
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 行，应用可以据此区分更新成功和资源不存在。

这里没有直接修改 status，因为前文已经约定：任务状态变化还要在同一个事务中写入 task_status_history。RETURNING 只负责返回行，不能替代这条业务完整性规则。

ON CONFLICT 主要用来处理唯一索引或唯一约束引起的冲突。例如，tasks 表已有 tasks_project_number_key 唯一约束，可以把同一项目下的 task_number = 1000 当作本次导入的幂等键：

01
INSERT INTO tasks (project_id, task_number, title, description)
02
VALUES (
03
    :project_id,
04
    1000,
05
    '整理 PostgreSQL 笔记',
06
    '由外部导入任务同步'
07
)
08
ON CONFLICT ON CONSTRAINT tasks_project_number_key
09
DO UPDATE SET
10
    title = EXCLUDED.title,
11
    description = EXCLUDED.description,
12
    updated_at = now()
13
RETURNING id, project_id, task_number, title, updated_at;

EXCLUDED 表示本次原本准备插入的行。没有冲突时 PostgreSQL 执行插入；发生指定的唯一冲突时，它原子地执行更新。其他约束错误仍会正常报错，不会被 ON CONFLICT 吞掉。

不指定冲突目标的 DO NOTHING 还可以忽略排他约束冲突，但排他约束不能作为 DO UPDATE 的仲裁条件。需要更新时，应明确选择一个非延迟的唯一约束或唯一索引。

如果只需要确保标签存在，而不希望为了取得 ID 产生一次无意义更新，可以先尝试插入：

1
INSERT INTO tags (name)
2
VALUES ('PostgreSQL')
3
ON CONFLICT (name) DO NOTHING
4
RETURNING id, name;

插入成功时，RETURNING 会返回新标签；发生冲突并执行 DO NOTHING 时，它返回 0 行，应用再执行一次参数化 SELECT 读取现有标签：

1
SELECT id, name
2
FROM tags
3
WHERE name = 'PostgreSQL';

在默认的 READ COMMITTED 隔离级别下，后面的独立 SELECT 会取得新的语句快照。不要为了追求“单条 SQL”就随意把插入和回查压进一个 CTE：并发事务刚提交冲突行时，同一条语句的快照未必能看见那一行。若冲突行随后又被另一个事务删除，应用还需要根据结果决定是否重试。

真实应用应把标签名作为参数传入。这里使用固定值只是为了展示 SQL 结构。