PostgreSQL 基础 SQL

PostgreSQL 入门不能只照搬 MySQL 语法。它有自己的数据类型、返回语法、分页方式、JSONB、数组、序列、函数和约束习惯。

一句话理解：

PostgreSQL 的基础 SQL 不只是增删改查，它的强项在于类型系统、约束、returning、on conflict、JSONB、数组、窗口函数和丰富索引。写 SQL 时要同时考虑表达能力、约束兜底、执行计划和 MVCC。

学习目标

学完这一页，你要能做到：

1. 创建数据库、表、主键、唯一约束和索引。
2. 写基础增删改查。
3. 理解 serial、bigserial、identity 的用途。
4. 使用 jsonb、数组、时间类型。
5. 会用 returning 拿到写入后的结果。
6. 能解释 timestamptz 和 timestamp 的区别。
7. 能写 on conflict 并知道唯一约束是并发兜底。
8. 能写游标分页、条件更新和事务 Demo。

一条 PostgreSQL 写入 SQL 做了什么

flowchart TD
    A["应用发送 insert/update"] --> B["解析 SQL"]
    B --> C["检查表、列、权限和约束"]
    C --> D["优化器生成执行计划"]
    D --> E["执行器访问 heap 和索引"]
    E --> F["生成 WAL"]
    F --> G["写入或生成新 tuple"]
    G --> H["提交事务"]
    H --> I["返回 returning 结果"]

这个流程说明：

1. 约束不是摆设，数据库会在写入时真正检查。
2. PostgreSQL 更新通常生成新 tuple，不是简单原地覆盖。
3. WAL 保证崩溃后能恢复。
4. returning 可以把写入后的值直接返回给应用。

PostgreSQL 常用类型

类型	场景	原理和注意点
bigint	业务 ID、外键 ID	范围大，适合长期增长
bigserial	自增大整数，常用于主键	本质上关联 sequence，可能跳号
generated ... as identity	SQL 标准身份列	新项目可优先考虑
numeric(10,2)	金额，精确小数	不要用 float 存钱
varchar(n)	有长度限制的字符串	限制输入长度，适合编号、名称
text	长文本	PostgreSQL 中 text 常用，但不要放高频列表大字段
timestamp	不带时区时间	只是日期时间值，不保存时区语义
timestamptz	带时区语义时间点	生产跨时区更推荐
jsonb	可查询的 JSON 二进制格式	支持 GIN 索引和包含查询
uuid	UUID	分布式生成方便，但随机 UUID 索引局部性差
boolean	布尔值	可为 true/false/null，注意 null 语义
text[]	字符串数组	适合少量标签，不适合复杂关系替代表

PostgreSQL 的类型系统比 MySQL 更丰富，这也是它适合复杂业务建模的原因之一。

timestamp 和 timestamptz

timestamp 保存的是“没有时区语义的日期时间”，timestamptz 保存的是一个时间点，展示时会按会话时区转换。

商业建议：

1. 跨时区系统、日志、订单支付、采集时间优先用 timestamptz。
2. 只表达本地日程、营业时间这类“墙上时间”时，可以考虑 timestamp。
3. Java 侧建议用 Instant 或明确时区的类型映射，避免服务器时区变化造成偏差。

建表 Demo

create table app_user (
  id bigserial primary key,
  username varchar(64) not null,
  mobile varchar(20) not null,
  status smallint not null default 1,
  tags text[] not null default '{}',
  profile jsonb not null default '{}',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint uk_app_user_mobile unique (mobile)
);

create index idx_app_user_status_created
on app_user(status, created_at desc);

create index idx_app_user_profile
on app_user using gin(profile);

为什么这样设计：

设计	原因
bigserial primary key	自动生成趋势递增 ID
mobile unique	由数据库保证手机号唯一
tags text[]	PostgreSQL 原生支持数组
profile jsonb	可存灵活属性，并能配合 GIN 索引查询
timestamptz	保存带时区语义的时间点

bigserial、sequence、identity 区别

方式	说明	注意点
serial	4 字节自增伪类型	老项目常见，范围较小
bigserial	8 字节自增伪类型	本质创建 sequence 并设置默认值
identity	SQL 标准身份列	新项目更标准
手动 sequence	显式 nextval	灵活，但要自己管理

bigserial 不是一个真正的底层类型，它会帮你创建 sequence。sequence 和 Oracle 类似，不保证连续。事务回滚、插入冲突、缓存等都可能导致跳号。

create table asset_id_demo (
  id bigint generated by default as identity primary key,
  name text not null
);

主键 ID 的目标是唯一和高效定位，不应该要求连续无缺口。业务编号要单独设计。

Insert 和 Returning

PostgreSQL 支持 returning，写入后直接返回生成的字段。

insert into app_user(username, mobile, profile)
values ('Tom', '13800138000', '{"city":"Hangzhou","level":3}')
returning id, created_at;

这在接口创建资源时很常用：一次 SQL 完成插入并拿到主键。

returning 为什么有价值

没有 returning 时，应用可能要 insert 后再查一次：

select currval('app_user_id_seq');

或者按唯一键再查：

select id from app_user where mobile = '13800138000';

二次查询会让代码更绕，也可能遇到并发和事务边界问题。returning 直接返回本次写入结果，更清晰。

更新也可以返回：

update app_user
set status = 2,
    updated_at = now()
where mobile = '13800138000'
  and status = 1
returning id, status, updated_at;

如果没有返回行，说明用户不存在或状态不允许更新。

Upsert

手机号重复时更新用户名：

insert into app_user(username, mobile, profile)
values ('Tom', '13800138000', '{"city":"Hangzhou"}')
on conflict (mobile)
do update set
  username = excluded.username,
  profile = excluded.profile,
  updated_at = now()
returning id;

excluded 表示本次尝试插入的新值。

Upsert 为什么必须依赖唯一约束

on conflict (mobile) 能工作，是因为 mobile 上有唯一约束或唯一索引。它不是应用层的 if else，而是数据库在并发写入时用唯一约束做冲突检测。

flowchart TD
    A["两个请求同时插入同一 mobile"] --> B["数据库检查唯一约束"]
    B --> C{"是否冲突"}
    C -- "不冲突" --> D["插入成功"]
    C -- "冲突" --> E["执行 do update 或 do nothing"]

如果只靠应用先查再插入：

请求1：查不到 -> 准备插入
请求2：查不到 -> 准备插入
请求1：插入成功
请求2：也插入，若无唯一约束就重复

所以商业项目里唯一性必须由数据库约束兜底。

JSONB 查询

select id, username
from app_user
where profile @> '{"city":"Hangzhou"}';

@> 表示左侧 JSON 是否包含右侧 JSON。配合 GIN 索引可以加速。

取 JSON 字段：

select
  id,
  profile ->> 'city' as city
from app_user;

操作符	含义
->	返回 JSON 值
->>	返回文本值
@>	JSON 包含

JSONB 适合什么，不适合什么

JSONB 适合保存“不同类型对象有少量差异字段”的灵活属性，比如资产扩展属性、设备采集原始属性、第三方回调原文。

不适合把核心关系模型全部塞进去：

适合 JSONB	不适合 JSONB
资产扩展属性	订单主表核心字段
第三方回调原文	金额、状态、用户 ID
少量查询条件	高频 Join 字段
半结构化配置	强约束业务关系

原因是 JSONB 虽然能查，但约束、Join、统计信息、字段演进、权限审计都不如普通列清晰。核心字段应该拆成列。

数组查询

select id, username
from app_user
where tags @> array['vip'];

表示 tags 数组包含 vip。

数组适合少量标签、简单枚举集合。不适合保存复杂多对多关系。比如用户标签可以用数组，但用户和角色、资产和科室权限这类需要管理、审计、Join 的关系，还是应该用关系表。

分页

普通分页：

select id, username, created_at
from app_user
where status = 1
order by created_at desc
limit 20 offset 10000;

深分页仍然可能慢，因为数据库要跳过前面大量数据。

更推荐游标分页：

select id, username, created_at
from app_user
where status = 1
  and created_at < timestamptz '2026-07-01 10:00:00+08'
order by created_at desc
limit 20;

深分页为什么慢

limit 20 offset 100000 不是直接跳到第 100001 行。数据库仍要按过滤和排序规则找到前 100020 行，再丢掉前 100000 行。

flowchart TD
    A["limit 20 offset 100000"] --> B["扫描或索引遍历满足条件的行"]
    B --> C["按 order by 得到顺序"]
    C --> D["跳过前 100000 行"]
    D --> E["返回 20 行"]

商业系统中：

1. 用户滚动列表、消息、采集记录适合游标分页。
2. 后台任意跳页要限制最大页码和筛选条件。
3. 大结果导出应走异步任务，不应同步深分页。

商业场景：资产平台灵活属性

资产系统中，不同资产类型字段不同：

create table asset (
  id bigserial primary key,
  asset_no varchar(64) not null,
  asset_type varchar(32) not null,
  owner_id bigint not null,
  attrs jsonb not null default '{}',
  created_at timestamptz not null default now(),
  unique (asset_no)
);

create index idx_asset_owner_created
on asset(owner_id, created_at desc);

create index idx_asset_attrs
on asset using gin(attrs);

查询“CPU 核数为 8 的服务器资产”：

select id, asset_no
from asset
where asset_type = 'server'
  and attrs @> '{"cpuCore":8}';

商业 Demo：资产创建、去重、返回结果

create table asset_import_record (
  id bigserial primary key,
  asset_no varchar(64) not null,
  import_batch_no varchar(64) not null,
  raw_payload jsonb not null,
  created_at timestamptz not null default now(),
  unique (asset_no, import_batch_no)
);

insert into asset_import_record(asset_no, import_batch_no, raw_payload)
values (
  'A-20260706-001',
  'BATCH-20260706',
  '{"source":"his","cpuCore":8,"memoryGb":32}'
)
on conflict (asset_no, import_batch_no)
do update set
  raw_payload = excluded.raw_payload,
  created_at = now()
returning id, asset_no, created_at;

这个 Demo 体现：

1. 批次内同一资产不重复。
2. JSONB 保存原始扩展属性。
3. on conflict 处理重复导入。
4. returning 返回写入结果。

事务 Demo：状态流转

begin;

update asset
set attrs = attrs || '{"checked":true}'::jsonb
where asset_no = 'A-20260706-001'
  and attrs @> '{"source":"his"}'
returning id, asset_no;

insert into asset_import_record(asset_no, import_batch_no, raw_payload)
values ('A-20260706-001', 'CHECK-20260706', '{"action":"CHECK"}');

commit;

如果中间任何一步失败，要 rollback。在应用中应由事务管理器控制提交和回滚，不要让状态更新成功但审计记录失败。

常见坑和排查

问题	表现	原因	处理
bigserial 跳号	ID 不连续	sequence 回滚或缓存	不要求技术 ID 连续
JSONB 滥用	查询、约束、Join 变复杂	核心字段没拆列	核心字段列化，扩展字段 JSONB
深分页慢	页码越大越慢	offset 要跳过大量行	游标分页或异步导出
returning 没返回	更新结果为空	条件不满足或状态已变化	检查影响行数并返回业务失败
数组替代表关系	权限和 Join 难做	模型边界错	多对多用关系表
时间错乱	跨时区显示不一致	类型和时区语义不清	统一 timestamptz 和应用时区策略

面试标准回答

PostgreSQL 基础 SQL 和 MySQL 类似，但类型系统和语法更丰富。它支持 bigserial、identity、jsonb、数组、uuid、timestamptz 等类型；insert/update/delete 可以使用 returning 返回变更后的数据；on conflict 可以基于唯一约束实现 upsert；jsonb 可以用 @>、->、->> 查询，并通过 GIN 索引优化。分页仍然要注意深分页问题，商业项目中更推荐游标分页。设计时要把核心字段拆成普通列，把灵活扩展字段放 JSONB，并用数据库约束兜底并发一致性。

关联学习：索引与优化器、事务与锁。