PHP小码农

Appearance

创建表（CREATE TABLE） ​

CREATE TABLE 是 DDL 中使用频率最高的一条语句。本节把它的三部分讲透：列定义（名字 + 类型）、约束（列约束与表约束两套写法）、以及与普通表并列的几个变体（GENERATED 列、LIKE、INHERITS、存储参数）。

前置知识

阅读本节前，建议先了解：模式（Schema）管理

基础概念 ​

一条 CREATE TABLE 语句在逻辑上由四块组成：

text
CREATE TABLE [IF NOT EXISTS] 表名 (
    列名 类型 [列约束...],        ← ① 列定义（Column Definition）
    列名 类型 [列约束...],
    [表级约束...],                ← ② 表约束（Table Constraint）
    ...
) [INHERITS (父表)]              ← ③ 继承
  [PARTITION BY ...]             ← ④ 分区（后续章节）
  [WITH (存储参数)]              ← ⑤ 存储参数
  [TABLESPACE 表空间];

几个必须分清的概念：

  • 列约束（Column Constraint）：直接写在列名后面的约束，是表约束的语法糖，但只能引用本列。
  • 表约束（Table Constraint）：写在所有列定义之后，可以引用多列（如复合主键）。唯一必须用表约束形式的约束是多列约束。
  • 系统列（System Column）：每张表自动拥有 ctid、xmin、xmax、cmin、cmax、tableoid 六个隐藏列，不能显式建，但可以 SELECT。
  • 对象标识符 OID（Object Identifier）：PostgreSQL 内部用 oid 标识每个对象，但用户表默认不含 oid 列（WITH OIDS 早在 PostgreSQL 12 就被移除）。

PostgreSQL 的约束类型与行为对照：

约束作用是否自动建索引允许 NULL
NOT NULL值不得为空否—
DEFAULT未提供值时填默认值否是
CHECK值必须满足布尔表达式否是（NULL 视为通过）
UNIQUE值（或组合）唯一是（B-tree 唯一索引）是（多个 NULL 不冲突）
PRIMARY KEYNOT NULL + UNIQUE是否
REFERENCES外键引用另一表否（引用侧不自动建索引）是
EXCLUDE用运算符排除冲突（如排期不重叠）是（需 USING gist 等）视运算符而定

NULL 与唯一约束的关系是最容易踩的坑：SQL 标准认为 NULL 不等于任何值，因此唯一索引里可以存在多个 NULL。想要「非空且唯一」就必须显式加 NOT NULL，这也是几乎所有业务唯一键都需要 NOT NULL 的原因。

语法与用法 ​

完整语法骨架（省略分区与继承细节）：

text
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name (
    column_name data_type
        [ COMPRESSION compression_method ]        -- PostgreSQL 14+
        [ COLLATE collation ]
        [ STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } ]
        [ COMPRESSION ... ]
        [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ]
    | INHERITS ( parent_table [, ... ] )
    [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( ... ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

-- 列约束的三种写法
[ CONSTRAINT constraint_name ]
{ NOT NULL [ NO INHERIT ] | NULL |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH ... ] [ ON DELETE action ] [ ON UPDATE action ] |
  CHECK ( expression ) [ NO INHERIT ] |
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ...] ) }

-- 表约束
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE ... |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn ) ] ... }

最简可运行示例：

sql
-- 最小表：只有主键与两个普通列
CREATE TABLE demo (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text   NOT NULL
);
text
CREATE TABLE
sql
-- 生产级示例：把常用约束一次写全
CREATE TABLE IF NOT EXISTS users (
  id         bigint       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text         NOT NULL,
  nickname   text         NOT NULL DEFAULT '',
  age        integer      CHECK (age >= 0 AND age <= 200),
  status     text         NOT NULL DEFAULT 'active',
  balance    numeric(12, 2) NOT NULL DEFAULT 0 CHECK (balance >= 0),
  tags       text[]       NOT NULL DEFAULT '{}',
  profile    jsonb        NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz  NOT NULL DEFAULT now(),
  updated_at timestamptz  NOT NULL DEFAULT now(),

  -- 表约束：可跨列
  CONSTRAINT uq_users_email     UNIQUE (email),
  CONSTRAINT ck_users_status    CHECK (status IN ('active', 'disabled', 'banned')),
  CONSTRAINT ck_users_nickname  CHECK (length(nickname) <= 64)
);

详细说明 ​

列定义：类型、排序规则与存储方式 ​

列定义最少要写「列名 + 类型」，其余都是可选项。

sql
CREATE TABLE demo_types (
  -- 类型可以带精度/长度修饰
  price      numeric(10, 2),            -- 总位数 10，小数 2 位
  name       varchar(64),               -- 变长字符串，带长度上限
  payload    jsonb,                     -- 二进制 JSON
  tags       text[],                    -- 数组
  ip_addr    inet,                      -- IPv4/IPv6 地址
  dur        interval,                  -- 时间间隔
  created_at timestamptz DEFAULT now(), -- 带时区时间戳

  -- 按列指定排序规则（PostgreSQL 12+ 支持）
  code_c     text COLLATE "C",          -- 字节序比较，适合纯 ASCII
  name_zh    text COLLATE "zh_CN.UTF-8",-- 指定中文排序

  -- 按列指定 TOAST 存储策略
  big_doc    text STORAGE EXTENDED,     -- 默认：可压缩、可行外
  small_doc  text STORAGE MAIN,         -- 尽量留在行内，不优先行外

  -- 按列指定压缩算法（PostgreSQL 14+）
  log_text   text COMPRESSION lz4       -- 比默认 pglz 更快、压缩率接近
);

存储策略的取舍：

策略含义适用场景
PLAIN禁止 TOAST，必须行内存放短字段，如 integer、date
EXTENDED允许压缩 + 允许行外（默认）大多数变长字段
EXTERNAL允许行外，不压缩已压缩内容（图片、gzip）、jsonb 有时更快
MAIN优先压缩行内，最后才考虑行外中等长度文本，希望读取少一次跳转
sql
-- 查看某列的存储策略
SELECT attname, attstorage, attcompression
FROM pg_attribute
WHERE attrelid = 'demo_types'::regclass AND attnum > 0;
text
  attname   | attstorage | attcompression
------------+------------+----------------
 price      | m          | 
 name       | x          | 
 payload    | x          | 
 tags       | x          | 
 ip_addr    | m          | 
 dur        | p          | 
 created_at | p          | 
 code_c     | x          | 
 name_zh    | x          | 
 big_doc    | x          | 
 small_doc  | m          | 
 log_text   | x          | l
(12 rows)

存储策略代码含义：p = PLAIN，e = EXTERNAL，m = MAIN，x = EXTENDED。

列约束 vs 表约束：什么时候必须用表约束 ​

单列能表达的约束，两种写法完全等价：

sql
-- 写法 A：列约束
CREATE TABLE t_col (
  id    bigint NOT NULL,
  email text   NOT NULL UNIQUE,
  age   integer CHECK (age >= 0)
);

-- 写法 B：表约束（语义完全相同）
CREATE TABLE t_tab (
  id    bigint,
  email text   NOT NULL,
  age   integer,
  PRIMARY KEY (id),
  UNIQUE (email),
  CHECK (age >= 0)
);

但以下三种情况只能用表约束：

场景原因示例
多列主键 / 唯一跨列组合PRIMARY KEY (tenant_id, order_no)
多列外键复合引用FOREIGN KEY (a, b) REFERENCES t (x, y)
跨列 CHECK表达式引用多列CHECK (end_at > start_at)
sql
CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id  bigint NOT NULL,
  order_no   text   NOT NULL,
  start_at   timestamptz NOT NULL,
  end_at     timestamptz NOT NULL,

  -- 多列主键（只能写在表约束位置）
  PRIMARY KEY (tenant_id, order_no),
  -- 跨列 CHECK（只能写在表约束位置）
  CONSTRAINT ck_orders_time CHECK (end_at > start_at),
  -- 复合外键（只能写在表约束位置）
  CONSTRAINT fk_orders_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON DELETE CASCADE
);

命名约束是一条重要的运维习惯。未命名时 PostgreSQL 会自动生成 users_email_key、users_pkey、users_age_check 这类名字，一旦要 ALTER TABLE ... DROP CONSTRAINT 就得先查名字：

sql
-- 查看表上的约束及其自动生成名
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'users'::regclass
ORDER BY contype, conname;
text
     conname      | contype |                   definition
------------------+---------+-------------------------------------------------
 users_pkey       | p       | PRIMARY KEY (id)
 uq_users_email   | u       | UNIQUE (email)
 ck_users_status  | c       | CHECK (status = ANY (ARRAY['active'::text, ...]))
 ck_users_nickname| c       | CHECK (length(nickname) <= 64)
(4 rows)

contype 取值对照：

contype含义
p主键（Primary Key）
u唯一（Unique）
c检查（Check）
f外键（Foreign Key）
x排他（Exclude）
t约束触发器（Constraint Trigger）

GENERATED：IDENTITY 与 STORED 生成列 ​

PostgreSQL 提供两种 GENERATED，用途完全不同：

特性语法何时计算可否写入
标识列（Identity）GENERATED ALWAYS AS IDENTITY插入时取值ALWAYS 不可写（需 OVERRIDING SYSTEM VALUE）；BY DEFAULT 可写
生成列（Generated Column）GENERATED ALWAYS AS (expr) STORED插入/更新时计算不可写
序列（Sequence）serial / bigserial插入时取值可写

标识列（Identity Column） 是 serial 的标准替代品，PostgreSQL 10 引入：

sql
CREATE TABLE demo_identity (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- 严格模式
  seq_no   bigint GENERATED BY DEFAULT AS IDENTITY,           -- 宽松模式
  name     text   NOT NULL
);

INSERT INTO demo_identity (name) VALUES ('a'), ('b');

-- 宽松模式允许显式指定
INSERT INTO demo_identity (seq_no, name) VALUES (100, 'c');

-- 严格模式必须显式声明才允许覆盖
INSERT INTO demo_identity (id, name) OVERRIDING SYSTEM VALUE VALUES (50, 'd');
sql
-- 错误！ALWAYS 类型不能显式赋值
INSERT INTO demo_identity (id, name) VALUES (99, 'e');
text
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

两种写法对比：

对比项GENERATED ... AS IDENTITYserial / bigserial
标准符合性SQL 标准PostgreSQL 方言
底层实现自动创建并 OWNED BY 序列同理，但序列是独立对象
权限序列归属表，删除表自动清理同样
能否被误删序列不易（内部依赖）可能（序列是独立对象）
显式插入值ALWAYS 需 OVERRIDING，BY DEFAULT 直接写直接写
推荐度推荐兼容老代码时使用

生成列（Generated Column / Stored Generated Column） 是 PostgreSQL 12 引入的存值生成列：

sql
CREATE TABLE demo_generated (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  qty        integer      NOT NULL CHECK (qty > 0),
  unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0),
  -- 生成列：值在写入时计算并物理存储
  amount     numeric(12,2) GENERATED ALWAYS AS (qty * unit_price) STORED,
  name       text NOT NULL,
  name_len   integer GENERATED ALWAYS AS (length(name)) STORED
);

INSERT INTO demo_generated (qty, unit_price, name) VALUES (3, 12.50, '机械键盘');
SELECT id, qty, unit_price, amount, name, name_len FROM demo_generated;
text
 id | qty | unit_price | amount |   name   | name_len
----+-----+------------+--------+----------+----------
  1 |   3 |      12.50 |  37.50 | 机械键盘 |        4
(1 row)

三个关于生成列的硬性限制：

sql
-- 错误！生成列不能显式赋值
INSERT INTO demo_generated (qty, unit_price, name, amount) VALUES (1, 1.00, 'x', 99);
text
ERROR:  cannot insert a non-DEFAULT value into column "amount"
DETAIL:  Column "amount" is a generated column.
sql
-- 错误！生成列表达式必须 IMMUTABLE，now() 不是
CREATE TABLE bad_gen (
  created_at timestamptz NOT NULL DEFAULT now(),
  day_str    text GENERATED ALWAYS AS (created_at::date::text) STORED
);
text
ERROR:  generation expression is not immutable
sql
-- 正确：用不可变函数组合
CREATE TABLE ok_gen (
  created_at timestamptz NOT NULL DEFAULT now(),
  day_str    text GENERATED ALWAYS AS (to_char(created_at, 'YYYY-MM-DD')) STORED
);
text
CREATE TABLE

另外，生成列不能被 ALTER TABLE ... ALTER COLUMN TYPE 直接改类型，也不能用 SET DEFAULT——它们只由表达式决定。

IF NOT EXISTS、LIKE 与 INHERITS ​

IF NOT EXISTS 只检查表名是否存在，不比对结构。它带来幂等，也带来「结构悄然不符」的风险：

sql
-- 第一次：真正创建
CREATE TABLE IF NOT EXISTS demo_cache (id int PRIMARY KEY, val text);
text
CREATE TABLE
sql
-- 第二次：静默跳过，注意下方 NOTICE
CREATE TABLE IF NOT EXISTS demo_cache (id int PRIMARY KEY, val text, extra jsonb);
text
NOTICE:  relation "demo_cache" already exists, skipping
CREATE TABLE

extra 列没有被创建，也不会有任何报错。迁移脚本里 IF NOT EXISTS 只适合「已知结构一致、只需确认存在」的场景。

CREATE TABLE ... LIKE 复制结构，是「克隆表」的首选方式：

text
CREATE TABLE new_table (LIKE source_table [ like_option ... ])

like_option 取值：
  INCLUDING COMMENTS        -- 复制列/表注释
  INCLUDING COMPRESSION     -- 复制列压缩设置（PostgreSQL 14+）
  INCLUDING CONSTRAINTS     -- 复制 CHECK 约束（不含 NOT NULL）
  INCLUDING DEFAULTS        -- 复制默认值
  INCLUDING GENERATED       -- 复制生成列表达式
  INCLUDING IDENTITY        -- 复制标识列
  INCLUDING INDEXES         -- 复制索引与主键/唯一/排他约束
  INCLUDING STATISTICS      -- 复制扩展统计
  INCLUDING STORAGE         -- 复制存储参数（含 TOAST）
  INCLUDING ALL             -- 上述全部
  EXCLUDING ...             -- 反向排除
sql
-- 只复制结构骨架（列名与类型）
CREATE TABLE users_bare (LIKE users);

-- 复制一份带全部属性的备份表
CREATE TABLE users_backup (LIKE users INCLUDING ALL);

-- 复制结构但不要索引（导入数据后再建索引更快）
CREATE TABLE users_stage (LIKE users INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

关键差异必须知道：LIKE 默认不复制外键、不复制 NOT NULL（NOT NULL 在 INCLUDING CONSTRAINTS 之外，需额外注意——PostgreSQL 中 NOT NULL 既不在 INCLUDING CONSTRAINTS 也不在 INCLUDING DEFAULTS，它只在 INCLUDING ALL 时随 INCLUDING CONSTRAINTS 一起复制）。

sql
-- 验证：不带 INCLUDING ALL 时 NOT NULL 会不会被复制
CREATE TABLE src_t (
  id   bigint  NOT NULL,
  note text    NOT NULL DEFAULT 'n',
  ck   integer,
  CONSTRAINT ck_src CHECK (ck > 0)
);

CREATE TABLE dst_bare (LIKE src_t);
CREATE TABLE dst_all  (LIKE src_t INCLUDING ALL);

SELECT
  (SELECT count(*) FROM pg_attribute WHERE attrelid = 'dst_bare'::regclass AND attnotnull) AS bare_notnull,
  (SELECT count(*) FROM pg_attribute WHERE attrelid = 'dst_all'::regclass  AND attnotnull) AS all_notnull;
text
 bare_notnull | all_notnull
--------------+-------------
            0 |           2
(1 row)

继承表（INHERITS） 是 PostgreSQL 特有的表分区前身，父表的列会被子表继承：

sql
CREATE TABLE vehicles (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  brand text   NOT NULL,
  price numeric(12, 2) NOT NULL CHECK (price >= 0)
);

CREATE TABLE cars (
  doors integer NOT NULL DEFAULT 4
) INHERITS (vehicles);

CREATE TABLE trucks (
  payload_kg numeric(10, 2) NOT NULL
) INHERITS (vehicles);
sql
-- 默认查询父表会连子表一起查出（这是继承最需要警惕的行为）
INSERT INTO vehicles (brand, price) VALUES ('通用', 100);
INSERT INTO cars     (brand, price, doors) VALUES ('大众', 200, 4);
INSERT INTO trucks   (brand, price, payload_kg) VALUES ('解放', 500, 8000);

SELECT tableoid::regclass AS src_table, id, brand, price FROM vehicles ORDER BY id;
text
 src_table | id | brand | price
-----------+----+-------+--------
 vehicles  |  1 | 通用  |    100
 cars      |  2 | 大众  |    200
 trucks    |  3 | 解放  |    500
(3 rows)
sql
-- 只看父表本身，用 ONLY
SELECT tableoid::regclass AS src_table, id, brand FROM ONLY vehicles;
text
 src_table | id | brand
-----------+----+-------
 vehicles  |  1 | 通用
(1 row)

继承的几个重要限制：

限制说明
索引不继承父表索引不覆盖子表，子表需各自建
主键不继承父子表主键互相独立，全局不唯一
外键不继承引用 vehicles 的外键默认不会约束到子表
唯一约束跨表不生效这是官方明确指出的「继承的功能性缺口」
SELECT 默认含子表需用 ONLY 限定
TRUNCATE/DELETE 默认含子表同理，需 ONLY
sql
-- 错误理解！父表主键并不能阻止子表插入重复 id
INSERT INTO cars (id, brand, price, doors) VALUES (1, '重复ID', 300, 4);
SELECT tableoid::regclass AS src_table, id, brand FROM vehicles WHERE id = 1;
text
 src_table | id | brand
-----------+----+--------
 vehicles  |  1 | 通用
 cars      |  1 | 重复ID
(2 rows)

正因为这些缺口，新项目应优先使用声明式分区（Declarative Partitioning）而非 INHERITS 手工继承。INHERITS 现在的合理用途是「共享列定义」，而非「做分区」。

WITH 存储参数 ​

WITH (...) 允许按表粒度覆盖存储与 autovacuum 行为：

参数默认说明典型调整场景
fillfactor100页内预留空间百分比，为后续更新留位高频更新表设为 70~90
autovacuum_enabledtrue是否启用该表的 autovacuum只读大表或少变更表可关
autovacuum_vacuum_scale_factor0.2触发行数比例阈值大表调小（如 0.01）
autovacuum_vacuum_threshold50触发行数绝对阈值小表调大
autovacuum_analyze_scale_factor0.1统计信息更新比例阈值大表调小
autovacuum_vacuum_cost_delay2msautovacuum 的限流延迟繁忙表设 0 加速
toast_tuple_target2048触发 TOAST 的元组长度极端场景微调
parallel_workers0并行扫描的工作进程数分析型大表设 4~8
sql
-- 高频更新的计数器表：降低 fillfactor，提高 autovacuum 频率
CREATE TABLE counters (
  id      bigint PRIMARY KEY,
  cnt     bigint NOT NULL DEFAULT 0,
  updated timestamptz NOT NULL DEFAULT now()
) WITH (
  fillfactor                        = 80,
  autovacuum_vacuum_scale_factor    = 0.01,
  autovacuum_vacuum_threshold       = 100,
  autovacuum_analyze_scale_factor   = 0.005,
  autovacuum_vacuum_cost_delay      = 0
);
sql
-- 只读历史归档表：关闭 autovacuum 以省资源
CREATE TABLE order_archive_2025 (LIKE orders INCLUDING ALL)
  WITH (autovacuum_enabled = false);

-- 查看表上的存储参数（reloptions）
SELECT relname, reloptions FROM pg_class WHERE relname IN ('counters', 'order_archive_2025');
text
      relname       |                                    reloptions
--------------------+-----------------------------------------------------------------------------------
 counters           | {fillfactor=80,autovacuum_vacuum_scale_factor=0.01,autovacuum_vacuum_threshold=100,
                    |  autovacuum_analyze_scale_factor=0.005,autovacuum_vacuum_cost_delay=0}
 order_archive_2025 | {autovacuum_enabled=false}
(2 rows)
sql
-- 也可以事后修改
ALTER TABLE counters SET (fillfactor = 70);
ALTER TABLE counters RESET (autovacuum_vacuum_cost_delay);

fillfactor 的收益需要一次表重建才能完全体现（VACUUM FULL 或 pg_repack），建表时就设好是最省事的做法。

实战示例 ​

场景：为一个电商系统建立 users、products、orders 三张核心表，写全约束、索引考虑、存储参数、注释，然后验证约束真的起作用。

第 1 步：清理环境

sql
DROP TABLE IF EXISTS orders, products, users CASCADE;
text
DROP TABLE

第 2 步：建 users 表

sql
CREATE TABLE users (
  id         bigint        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text          NOT NULL,
  nickname   text          NOT NULL DEFAULT '',
  status     text          NOT NULL DEFAULT 'active',
  balance    numeric(12,2) NOT NULL DEFAULT 0,
  tags       text[]        NOT NULL DEFAULT '{}',
  profile    jsonb         NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz   NOT NULL DEFAULT now(),
  updated_at timestamptz   NOT NULL DEFAULT now(),

  CONSTRAINT uq_users_email    UNIQUE (email),
  CONSTRAINT ck_users_status   CHECK (status IN ('active', 'disabled', 'banned')),
  CONSTRAINT ck_users_balance  CHECK (balance >= 0),
  CONSTRAINT ck_users_nickname CHECK (length(nickname) <= 64),
  CONSTRAINT ck_users_profile  CHECK (jsonb_typeof(profile) = 'object')
);

第 3 步：建 products 表（含生成列与存储参数）

sql
CREATE TABLE products (
  id          bigint         GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text           NOT NULL,
  name        text           NOT NULL,
  price       numeric(10,2)  NOT NULL,
  cost        numeric(10,2)  NOT NULL DEFAULT 0,
  stock       integer        NOT NULL DEFAULT 0,
  -- 生成列：毛利率随 price/cost 自动更新，无需应用维护
  margin_rate numeric(8,4)   GENERATED ALWAYS AS (
    CASE WHEN price > 0 THEN (price - cost) / price ELSE 0 END
  ) STORED,
  is_active   boolean        NOT NULL DEFAULT true,
  created_at  timestamptz    NOT NULL DEFAULT now(),

  CONSTRAINT uq_products_sku     UNIQUE (sku),
  CONSTRAINT ck_products_price   CHECK (price >= 0),
  CONSTRAINT ck_products_cost    CHECK (cost  >= 0),
  CONSTRAINT ck_products_stock   CHECK (stock >= 0)
) WITH (
  fillfactor = 90,
  autovacuum_vacuum_scale_factor = 0.05
);

第 4 步：建 orders 表（含多列约束与复合外键）

sql
CREATE TABLE orders (
  id         bigint        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint        NOT NULL,
  order_no   text          NOT NULL,
  status     text          NOT NULL DEFAULT 'pending',
  qty        integer       NOT NULL,
  amount     numeric(12,2) NOT NULL,
  created_at timestamptz   NOT NULL DEFAULT now(),
  paid_at    timestamptz,

  CONSTRAINT uq_orders_no      UNIQUE (order_no),
  CONSTRAINT fk_orders_user    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  CONSTRAINT ck_orders_status  CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  CONSTRAINT ck_orders_qty     CHECK (qty > 0),
  CONSTRAINT ck_orders_amount  CHECK (amount >= 0),
  -- 跨列约束：已支付必须有支付时间，未支付必须没有
  CONSTRAINT ck_orders_paid    CHECK ((status = 'paid') = (paid_at IS NOT NULL))
);

-- 为外键列建索引（PostgreSQL 不会自动建）
CREATE INDEX idx_orders_user_id    ON orders (user_id);
CREATE INDEX idx_orders_created_at ON orders (created_at DESC);

第 5 步：写入数据并观察生成列

sql
INSERT INTO users (email, nickname, balance, tags, profile) VALUES
  ('alice@example.com', 'Alice', 1000.00, '{vip,beta}',  '{"theme": "dark"}'),
  ('bob@example.com',   'Bob',       0.00, '{}',          '{}');

INSERT INTO products (sku, name, price, cost, stock) VALUES
  ('KB-001', '机械键盘',    349.00, 180.00, 120),
  ('MS-002', '无线鼠标',    129.00,  60.00, 300),
  ('ST-003', '显示器支架',  259.00, 150.00,  45);

INSERT INTO orders (user_id, order_no, status, qty, amount, paid_at) VALUES
  (1, 'SO-20260924-001', 'paid',    2, 698.00, now()),
  (1, 'SO-20260924-002', 'pending', 10, 1290.00, NULL),
  (2, 'SO-20260924-003', 'shipped',  1, 259.00, now());
text
INSERT 0 2
INSERT 0 3
INSERT 0 3
sql
-- 生成列 margin_rate 自动算出
SELECT id, sku, name, price, cost, margin_rate, stock FROM products ORDER BY id;
text
 id |  sku   |    name     | price  |  cost  | margin_rate | stock
----+--------+-------------+--------+--------+-------------+-------
  1 | KB-001 | 机械键盘     | 349.00 | 180.00 |      0.4842 |   120
  2 | MS-002 | 无线鼠标     | 129.00 |  60.00 |      0.5349 |   300
  3 | ST-003 | 显示器支架   | 259.00 | 150.00 |      0.4208 |    45
(3 rows)

第 6 步：验证约束真的生效

sql
-- 6.1 唯一约束
INSERT INTO users (email) VALUES ('alice@example.com');
text
ERROR:  duplicate key value violates unique constraint "uq_users_email"
DETAIL:  Key (email)=(alice@example.com) already exists.
sql
-- 6.2 CHECK 约束
INSERT INTO users (email, status) VALUES ('x@example.com', 'unknown');
text
ERROR:  new row for relation "users" violates check constraint "ck_users_status"
DETAIL:  Failing row contains (3, x@example.com, , unknown, 0.00, {}, {}, 2026-09-24 11:02:03, ...).
sql
-- 6.3 跨列 CHECK
INSERT INTO orders (user_id, order_no, status, qty, amount, paid_at)
VALUES (1, 'SO-20260924-004', 'paid', 1, 100.00, NULL);
text
ERROR:  new row for relation "orders" violates check constraint "ck_orders_paid"
DETAIL:  Failing row contains (4, 1, SO-20260924-004, paid, 1, 100.00, 2026-09-24 11:02:10, null).
sql
-- 6.4 外键约束
INSERT INTO orders (user_id, order_no, status, qty, amount)
VALUES (999, 'SO-20260924-005', 'pending', 1, 10.00);
text
ERROR:  insert or update on table "orders" violates foreign key constraint "fk_orders_user"
DETAIL:  Key (user_id)=(999) is not present in table "users".
sql
-- 6.5 生成列不可写
INSERT INTO products (sku, name, price, cost, margin_rate) VALUES ('X-001', 'x', 10, 5, 0.9);
text
ERROR:  cannot insert a non-DEFAULT value into column "margin_rate"
DETAIL:  Column "margin_rate" is a generated column.

第 7 步：查看表的完整结构

sql
-- psql 元命令：\d+ 显示列、类型、默认值、生成表达式、存储参数
\d+ products
text
                                                       Table "public.products"
   Column    |           Type           | Collation | Nullable |           Default            | Storage  | Compression | Stats target | Description
-------------+--------------------------+-----------+----------+------------------------------+----------+-------------+--------------+-------------
 id          | bigint                   |           | not null | generated always as identity | plain    |             |              |
 sku         | text                     |           | not null |                              | extended |             |              |
 name        | text                     |           | not null |                              | extended |             |              |
 price       | numeric(10,2)            |           | not null |                              | main     |             |              |
 cost        | numeric(10,2)            |           | not null | 0                            | main     |             |              |
 stock       | integer                  |           | not null | 0                            | plain    |             |              |
 margin_rate | numeric(8,4)             |           | not null | generated always as (...) stored | main  |             |              |
 is_active   | boolean                  |           | not null | true                         | plain    |             |              |
 created_at  | timestamp with time zone |           | not null | now()                        | plain    |             |              |
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
    "uq_products_sku" UNIQUE CONSTRAINT, btree (sku)
Check constraints:
    "ck_products_cost" CHECK (cost >= 0::numeric)
    "ck_products_price" CHECK (price >= 0::numeric)
    "ck_products_stock" CHECK (stock >= 0)
Options: fillfactor=90, autovacuum_vacuum_scale_factor=0.05

第 8 步：清理

sql
DROP TABLE orders, products, users CASCADE;
text
DROP TABLE

注意事项 ​

  • IF NOT EXISTS 不比对结构。 名字存在就静默跳过，哪怕列完全不同。迁移脚本里应配合版本表使用，而不是依赖它。
sql
-- 危险！结构变更会被静默忽略
CREATE TABLE IF NOT EXISTS users (id bigint PRIMARY KEY, brand_new_col jsonb);
  • 唯一约束允许多个 NULL。 业务唯一键几乎总需要 NOT NULL。
sql
-- 建表时没加 NOT NULL，结果可以插入多行 NULL
CREATE TABLE t_u (code text UNIQUE);
INSERT INTO t_u VALUES (NULL), (NULL), (NULL);
SELECT count(*) FROM t_u;
text
 count
-------
     3
(3 rows)
sql
-- 正确：唯一键加 NOT NULL
CREATE TABLE t_u2 (code text NOT NULL UNIQUE);
  • CHECK 约束对 NULL 的处理是「通过」。 表达式结果为 NULL 时约束不报错。
sql
-- 期望「年龄必须 >= 18」，但 age 为 NULL 时能插入
CREATE TABLE t_age (age integer CHECK (age >= 18));
INSERT INTO t_age VALUES (NULL);   -- 成功！
text
INSERT 0 1
sql
-- 正确：需要非空就显式加 NOT NULL
CREATE TABLE t_age2 (age integer NOT NULL CHECK (age >= 18));
  • 外键不会自动给引用列建索引。 删父表行时需要扫描子表，没索引会导致父表 DELETE 极慢，甚至锁住整张子表。
sql
-- 建表后必须补上
CREATE INDEX idx_orders_user_id ON orders (user_id);
  • GENERATED ALWAYS AS (...) STORED 的表达式必须不可变（IMMUTABLE）。 不能用 now()、current_user、random() 等。
sql
-- 错误！generation expression is not immutable
CREATE TABLE bad (t timestamptz DEFAULT now(), d text GENERATED ALWAYS AS (now()::text) STORED);
  • 生成列不能改类型、不能加默认值、不能用 UPDATE 赋值。 修改表达式需要 DROP COLUMN + 重新 ADD COLUMN，会重写整表。
sql
-- 错误！
ALTER TABLE products ALTER COLUMN margin_rate SET DEFAULT 0;
text
ERROR:  column "margin_rate" of relation "products" is a generated column
  • INHERITS 的主键、唯一约束、索引、外键都不跨表生效。 这是官方文档明确列出的功能性缺口。做分区请用声明式分区。
sql
-- 危险！子表可以插入与父表重复的 id
SELECT tableoid::regclass, id FROM vehicles WHERE id = 1;
  • 默认查询会包含子表。 忘记 ONLY 会拿到比预期多的行，聚合结果也会偏大。
sql
-- 期望只统计父表，实际连子表一起算了
SELECT count(*) FROM vehicles;

-- 正确
SELECT count(*) FROM ONLY vehicles;
  • DROP TABLE 默认是 RESTRICT，有外键引用时会失败；CASCADE 会连带删除引用它的表约束，甚至删掉其他表（视依赖方向而定）。
sql
-- 危险！可能连锁删除
DROP TABLE users CASCADE;

  • 表名与列名长度上限 63 字节，超出静默截断。用长英文短语命名要小心。

  • fillfactor 只在建表或表重建后完全生效，事后 ALTER TABLE ... SET (fillfactor) 只影响新写入的页。

  • WITH OIDS 在 PostgreSQL 12 起已彻底移除。 老代码里的 WITH OIDS 需要改掉。

sql
-- 错误！PostgreSQL 12+ 不再支持
CREATE TABLE t_oid (id int) WITH OIDS;

最佳实践 ​

  • 主键统一用 bigint GENERATED ALWAYS AS IDENTITY，不用 serial（非标准、序列是独立对象易误删），也不用无意义的自然键。
sql
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
  • 所有约束显式命名。 自动生成的名字在 ALTER TABLE ... DROP CONSTRAINT、备份恢复、跨环境比对时都会带来麻烦。
sql
CONSTRAINT uq_users_email    UNIQUE (email),
CONSTRAINT ck_users_status   CHECK (status IN ('active', 'disabled')),
CONSTRAINT fk_orders_user    FOREIGN KEY (user_id) REFERENCES users (id)

  • 业务唯一键写成 NOT NULL UNIQUE 的组合，避免多 NULL 绕过唯一性。

  • 时间戳统一用 timestamptz，默认值 now()，不要用 timestamp（无时区）或 timestamp with time zone 的字符串字面量。

sql
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
  • 外键列必须建索引。 这是 PostgreSQL 与 MySQL 的一处重要差异。
sql
CREATE INDEX idx_orders_user_id ON orders (user_id);
  • 金额用 numeric(p, s)，绝不用 float/double precision。 浮点数会有舍入误差。
sql
-- 错误！0.1 + 0.2 != 0.3
-- amount double precision

-- 正确
amount numeric(12,2) NOT NULL CHECK (amount >= 0)
  • 把不变量写成 CHECK 约束，而不是只写在应用层。 数据库是最后一道防线，也是多写入路径下的唯一共同防线。
sql
CONSTRAINT ck_orders_paid CHECK ((status = 'paid') = (paid_at IS NOT NULL))
  • 高频更新表建表时就设 fillfactor（70~90），配合调快该表的 autovacuum，可显著减少膨胀。
sql
) WITH (fillfactor = 80, autovacuum_vacuum_scale_factor = 0.01)
sql
COMMENT ON TABLE  orders       IS '订单主表';
COMMENT ON COLUMN orders.qty   IS '购买数量，必须大于 0';

  • 结构变更走迁移脚本，不用 IF NOT EXISTS 掩盖差异。 用版本表（如 schema_migrations）记录已应用的迁移。

  • 克隆表结构用 LIKE ... INCLUDING ALL，但要知道它不复制外键，外键需要手工补。

sql
CREATE TABLE orders_2025 (LIKE orders INCLUDING ALL);
-- 外键仍需手工添加
ALTER TABLE orders_2025 ADD CONSTRAINT fk_orders_2025_user
  FOREIGN KEY (user_id) REFERENCES users (id);
  • 上线前用 \d+ 或查询 pg_constraint 复核一遍，确认约束与索引符合设计。
sql
SELECT conname, contype, pg_get_constraintdef(oid) AS def
FROM pg_constraint WHERE conrelid = 'orders'::regclass;

下一节

继续学习：修改表结构（ALTER TABLE）

参考链接 ​