创建表与插入数据

本节讲解如何使用 CREATE TABLE 定义表结构、如何用 INSERT 写入数据，以及 PostgreSQL 提供的常用列类型与约束。学完本节，你将能独立设计一张符合生产规范的表，并安全地写入数据。

前置知识

阅读本节前，建议先了解：数据库与模式的基本操作

基础概念

表（Table）由列（Column）和行（Row）组成。定义表就是定义列的名称、数据类型和约束。

概念	术语	说明
列	Column / Field	表的属性，必须声明数据类型
行	Row / Tuple	一条记录，在 PostgreSQL 内部称元组（Tuple）
主键	Primary Key（PK）	唯一标识一行，非空且唯一；一张表最多一个
外键	Foreign Key（FK）	引用另一张表的主键，保证引用完整性
约束	Constraint	对数据施加的规则，如非空、唯一、检查、外键
默认值	Default Value	插入时未提供该列值时使用的值
生成列	Generated Column	由其他列计算得出的列，分存储型与虚拟型
序列	Sequence	生成自增数字的对象，IDENTITY 背后即是序列
DDL	Data Definition Language	定义/修改结构的语句，如 CREATE、ALTER、DROP
DML	Data Manipulation Language	操作数据的语句，如 INSERT、UPDATE、DELETE

三个容易混淆的概念需要先区分：

• NULL 与 空字符串 ''：NULL 表示"未知"，参与运算结果仍为 NULL；空字符串是一个长度为零的真实值。PostgreSQL 严格区分二者，没有 MySQL 那种"把空字符串自动转为 NULL"的行为。
• SERIAL 与 IDENTITY：两者都能实现自增。SERIAL 是历史遗留写法（本质是 integer + 序列 + 默认值），IDENTITY 是 SQL 标准写法，权限与归属更清晰。新项目应使用 IDENTITY。
• 存储型生成列 与 虚拟生成列：存储型（STORED）在写入时计算并占用磁盘；虚拟（VIRTUAL）在读取时计算，不占存储。

语法与用法

CREATE TABLE 的基本语法：

CREATE TABLE [IF NOT EXISTS] 模式名.表名 (
    列名 数据类型 [列约束] [DEFAULT 默认值],
    列名 数据类型 [列约束] [DEFAULT 默认值],
    [表级约束]
);

INSERT 的基本语法：

INSERT INTO 表名 (列1, 列2, ...)
VALUES (值1, 值2, ...);

INSERT INTO 表名 (列1, 列2, ...)
SELECT ...;

INSERT INTO 表名 (列1, 列2, ...)
VALUES (...), (...), (...)              -- 多行插入
RETURNING 列1, 列2;                      -- 返回写入结果

常用数据类型速查：

类别	类型	说明	建议
整数	SMALLINT / INTEGER / BIGINT	2 / 4 / 8 字节	主键统一用 BIGINT
自增	GENERATED ALWAYS AS IDENTITY	标准自增	新项目首选
精确小数	NUMERIC(p, s)	精度可指定	金额必用
浮点	REAL / DOUBLE PRECISION	近似值	禁用于金额
字符串	TEXT / VARCHAR(n) / CHAR(n)	变长/定长	优先 TEXT，CHAR 少用
布尔	BOOLEAN	真/假/未知	优于 0/1
时间	TIMESTAMPTZ / TIMESTAMP / DATE / TIME	带/不带时区	建议 TIMESTAMPTZ
二进制	BYTEA	字节数组	大文件用对象存储
结构化	JSONB / JSON	二进制/文本 JSON	需要索引选 JSONB
数组	类型[]	任意类型的数组	元素数量少时适用
枚举	自定义 ENUM	受限取值集合	取值稳定时适用
UUID	UUID	全局唯一标识	18 起可用 uuidv7() 生成有序 UUID

最简可运行示例：

-- 创建一张用户表
CREATE TABLE users (
  id         BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      TEXT        NOT NULL UNIQUE,
  name       TEXT        NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 插入一行
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');

详细说明

列类型的选择

选类型的核心原则是：语义准确优先于存储节省。

CREATE TABLE products (
  id          BIGINT         GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         TEXT           NOT NULL UNIQUE,              -- 商品编码：可变长度
  name        TEXT           NOT NULL,                      -- 名称
  price       NUMERIC(10, 2) NOT NULL CHECK (price >= 0),   -- 金额：必须精确
  weight_kg   NUMERIC(8, 3),                                -- 重量：允许小数
  stock       INTEGER        NOT NULL DEFAULT 0 CHECK (stock >= 0),
  is_active   BOOLEAN        NOT NULL DEFAULT true,         -- 状态：布尔
  tags        TEXT[]         NOT NULL DEFAULT '{}',          -- 数组：标签
  attrs       JSONB          NOT NULL DEFAULT '{}',          -- 半结构化扩展属性
  created_at  TIMESTAMPTZ    NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ
);

为什么金额用 NUMERIC 而不用 DOUBLE PRECISION：

-- 浮点的舍入误差演示
SELECT
  0.1::DOUBLE PRECISION + 0.2::DOUBLE PRECISION AS float_sum,
  0.1::NUMERIC          + 0.2::NUMERIC          AS numeric_sum;

      float_sum      | numeric_sum
---------------------+-------------
 0.30000000000000004 |        0.30
(1 row)

TEXT 与 VARCHAR(n) 的取舍：

对比项	TEXT	VARCHAR(n)
存储	变长，只存实际长度	变长，只存实际长度
性能	完全相同	完全相同
长度限制	无	超出报错
适用场景	大多数场景	需要数据库层强制长度上限

-- 不推荐：用 VARCHAR 长度做"性能优化"，实际无收益且容易改不动
CREATE TABLE t_bad (
  name   VARCHAR(255),
  remark VARCHAR(255)
);

-- 推荐：TEXT + CHECK，业务规则显式表达
CREATE TABLE t_good (
  name   TEXT NOT NULL CHECK (length(name) BETWEEN 1 AND 64),
  remark TEXT
);

约束的种类与写法

约束是保证数据质量的最后一道防线，放在数据库层比放在应用层更可靠。

-- 约束类型汇总示例（语法演示，字段非业务合理）
CREATE TABLE constraint_demo (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- 主键
  email        TEXT        NOT NULL UNIQUE,                        -- 唯一 + 非空
  nickname     TEXT        UNIQUE,                                 -- 唯一但可为空
  age          INTEGER     CHECK (age BETWEEN 0 AND 150),          -- 检查
  status       TEXT        NOT NULL DEFAULT 'new'
                           CHECK (status IN ('new', 'active', 'blocked')),
  score        NUMERIC(5, 2) NOT NULL DEFAULT 0
                           CHECK (score >= 0),
  tenant_id    BIGINT      NOT NULL REFERENCES tenants (id)
                           ON DELETE CASCADE ON UPDATE CASCADE,     -- 外键
  -- 表级约束：跨列规则
  CONSTRAINT chk_age_score CHECK (age IS NULL OR score <= 100),
  -- 多列唯一
  CONSTRAINT uq_email_tenant UNIQUE (email, tenant_id)
);

约束	作用	允许 NULL	可否延迟检查
PRIMARY KEY	唯一标识行，自动建唯一索引	不允许	支持 DEFERRABLE
UNIQUE	值唯一，自动建唯一索引	允许多行 NULL	支持 DEFERRABLE
NOT NULL	禁止空值	—	不支持
CHECK	表达式必须为真或未知	未知视为通过	不支持
FOREIGN KEY	引用另一表，保证引用完整性	允许多行 NULL	支持 DEFERRABLE
EXCLUDE	排除重叠，常用于时间区间/房间预订	—	支持 DEFERRABLE

外键的级联行为必须显式选择：

行为	含义	适用场景
ON DELETE NO ACTION（默认）	有引用时禁止删除	强一致，最保守
ON DELETE RESTRICT	立即禁止删除	同 NO ACTION，但不可延迟
ON DELETE CASCADE	连带删除子行	从属数据（订单明细）
ON DELETE SET NULL	子行外键置空	关系可失去（可选归属）
ON DELETE SET DEFAULT	子行外键置默认值	需要有默认归属行

-- 演示：不同级联行为的效果
CREATE TABLE tenants (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT   NOT NULL
);

CREATE TABLE members (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id BIGINT NOT NULL REFERENCES tenants (id) ON DELETE CASCADE,
  email     TEXT   NOT NULL
);

INSERT INTO tenants (name) VALUES ('Acme') RETURNING id;

 id
----
  1
(1 row)

INSERT INTO members (tenant_id, email) VALUES (1, 'a@acme.com');

-- 删除租户会连带删除其成员（CASCADE 生效）
DELETE FROM tenants WHERE id = 1;
SELECT count(*) AS remaining FROM members;

DELETE 1

 remaining
-----------
         0
(1 row)

主键的三种写法

-- 写法一：SERIAL（历史写法，不推荐新项目使用）
CREATE TABLE t_serial (
  id SERIAL PRIMARY KEY          -- 等价于 integer + 序列 + DEFAULT nextval(...)
);

-- 写法二：IDENTITY（SQL 标准，推荐）
CREATE TABLE t_identity (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- 写法三：GENERATED BY DEFAULT AS IDENTITY（允许手工指定 id）
CREATE TABLE t_identity_by_default (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);

写法	类型	可否手工插入 id	序列归属	推荐度
SERIAL	integer（4 字节）	可以	弱绑定，容易残留孤立序列	不推荐
BIGSERIAL	bigint	可以	同上	不推荐
GENERATED ALWAYS AS IDENTITY	自选（如 BIGINT）	默认不可以（需 OVERRIDING SYSTEM VALUE）	强绑定，随表删除	推荐
GENERATED BY DEFAULT AS IDENTITY	自选	可以	强绑定	需手工导入时使用

-- ALWAYS 模式下若确实要指定 id，必须显式声明
INSERT INTO t_identity (id) OVERRIDING SYSTEM VALUE VALUES (100);

-- 查看序列当前值
SELECT currval(pg_get_serial_sequence('t_identity', 'id')) AS current_value;

生成列

生成列的值由其他列自动计算，适合做"冗余但可推导"的字段。

CREATE TABLE order_items (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku        TEXT           NOT NULL,
  qty        INTEGER        NOT NULL CHECK (qty > 0),
  unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0),
  -- 存储型生成列：写入时计算，可建索引
  line_total NUMERIC(12, 2) GENERATED ALWAYS AS (qty * unit_price) STORED
);

INSERT INTO order_items (sku, qty, unit_price) VALUES ('SKU-1001', 3, 199.00);

SELECT sku, qty, unit_price, line_total FROM order_items;

   sku    | qty | unit_price | line_total
----------+-----+------------+------------
 SKU-1001 |   3 |     199.00 |     597.00
(1 row)

-- 生成列不能被直接写入，这是它的保护机制
-- 错误！ERROR: cannot insert a non-DEFAULT value into column "line_total"
-- INSERT INTO order_items (sku, qty, unit_price, line_total) VALUES ('X', 1, 1.00, 1.00);

PostgreSQL 18 起支持虚拟生成列（默认即为虚拟），不占用存储：

-- PostgreSQL 18+
CREATE TABLE order_items_v18 (
  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),
  -- 18 起可写 VIRTUAL：读取时计算，不落盘
  line_total NUMERIC(12, 2) GENERATED ALWAYS AS (qty * unit_price) VIRTUAL
);

SELECT column_name, is_generated, generation_expression
FROM information_schema.columns
WHERE table_name = 'order_items_v18';

 column_name | is_generated | generation_expression
-------------+--------------+-----------------------
 line_total  | ALWAYS       | (qty * unit_price)
(1 row)

INSERT 的各种形式

CREATE TABLE users (
  id         BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      TEXT        NOT NULL UNIQUE,
  name       TEXT        NOT NULL,
  age        INTEGER     CHECK (age >= 0),
  city       TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

形式一：指定列的单行插入

INSERT INTO users (email, name, age, city)
VALUES ('alice@example.com', 'Alice', 28, 'Shanghai');

形式二：多行插入（比多次单行插入快得多）

INSERT INTO users (email, name, age, city)
VALUES
  ('bob@example.com',    'Bob',    35, 'Beijing'),
  ('carol@example.com',  'Carol',  31, 'Shenzhen'),
  ('dave@example.com',   'Dave',   42, 'Hangzhou'),
  ('erin@example.com',   'Erin',   26, 'Chengdu');

形式三：用 RETURNING 拿回写入结果

INSERT INTO users (email, name, age, city)
VALUES ('frank@example.com', 'Frank', 39, 'Wuhan')
RETURNING id, email, created_at;

 id |       email        |          created_at
----+--------------------+-------------------------------
  6 | frank@example.com  | 2026-09-21 11:12:03.881204+08
(1 row)

形式四：INSERT ... SELECT，把查询结果写入

CREATE TABLE users_backup (LIKE users INCLUDING ALL);

INSERT INTO users_backup (email, name, age, city)
SELECT email, name, age, city
FROM users
WHERE age >= 35
RETURNING id, email;

 id |       email
----+--------------------
  1 | bob@example.com
  2 | carol@example.com
  3 | dave@example.com
(3 rows)

注意 LIKE users INCLUDING ALL 会复制默认值与约束，但不会复制序列的独立状态，因此 users_backup 的 id 会从自己的序列开始。

形式五：ON CONFLICT 实现 UPSERT

-- 冲突时更新
INSERT INTO users (email, name, age, city)
VALUES ('alice@example.com', 'Alice Zhang', 29, 'Shanghai')
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name,
      age  = EXCLUDED.age,
      city = EXCLUDED.city
RETURNING id, email, name;

 id |       email        |     name
----+--------------------+--------------
  1 | alice@example.com  | Alice Zhang
(1 row)

-- 冲突时什么都不做
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Duplicate')
ON CONFLICT (email) DO NOTHING;

-- 拿到"Do Nothing"的结果需要用 RETURNING + 判断是否为空

形式六：INSERT ... VALUES 配合 DEFAULT

-- 显式使用列的默认值
INSERT INTO users (email, name, city) VALUES ('grace@example.com', 'Grace', DEFAULT);

-- 用 DEFAULT VALUES 插入一行全默认值（要求所有非默认列都可空或有默认）
INSERT INTO users DEFAULT VALUES;

查看与修改表结构

-- 查看列定义
SELECT
  ordinal_position            AS pos,
  column_name                 AS column,
  data_type                   AS type,
  is_nullable                 AS nullable,
  column_default              AS default_value,
  is_identity                 AS identity
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'users'
ORDER BY ordinal_position;

 pos |  column    |          type          | nullable | default_value | identity
-----+------------+------------------------+----------+---------------+----------
   1 | id         | bigint                 | NO       |               | YES
   2 | email      | text                   | NO       |               |
   3 | name       | text                   | NO       |               |
   4 | age        | integer                | YES      |               |
   5 | city       | text                   | YES      |               |
   6 | created_at | timestamp with time zone | NO     | now()         |
(6 rows)

-- 查看约束
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'users'::regclass
ORDER BY contype;

     conname      | contype |               definition
------------------+---------+----------------------------------------
 users_pkey       | p       | PRIMARY KEY (id)
 users_email_key  | u       | UNIQUE (email)
 users_age_check  | c       | CHECK ((age >= 0))
(3 rows)

-- 常见的结构修改
ALTER TABLE users ADD COLUMN phone TEXT;                       -- 加列
ALTER TABLE users ALTER COLUMN city SET NOT NULL;              -- 加非空约束（已有 NULL 会失败）
ALTER TABLE users ADD CONSTRAINT chk_city CHECK (length(city) <= 32);
ALTER TABLE users RENAME COLUMN phone TO mobile;               -- 列改名
ALTER TABLE users DROP COLUMN mobile;                          -- 删列

实战示例

场景：为一个电商系统设计商品与订单两张表，并灌入初始数据。要求类型规范、约束完整、可安全重跑。

第 1 步：建表（先父后子）

-- 商品表
CREATE TABLE IF NOT EXISTS products (
  id          BIGINT         GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         TEXT           NOT NULL UNIQUE,
  name        TEXT           NOT NULL CHECK (length(name) BETWEEN 1 AND 128),
  category    TEXT           NOT NULL DEFAULT 'uncategorized',
  price       NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  stock       INTEGER        NOT NULL DEFAULT 0 CHECK (stock >= 0),
  is_active   BOOLEAN        NOT NULL DEFAULT true,
  attrs       JSONB          NOT NULL DEFAULT '{}',
  created_at  TIMESTAMPTZ    NOT NULL DEFAULT now()
);

CREATE TABLE

-- 订单表：通过外键引用商品
CREATE TABLE IF NOT EXISTS orders (
  id          BIGINT         GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    TEXT           NOT NULL UNIQUE,
  sku         TEXT           NOT NULL REFERENCES products (sku) ON UPDATE CASCADE,
  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,
  status      TEXT           NOT NULL DEFAULT 'pending'
                             CHECK (status IN ('pending', 'paid', 'shipped', 'closed')),
  created_at  TIMESTAMPTZ    NOT NULL DEFAULT now()
);

CREATE TABLE

生成的两个关键约束：

约束名	类型	作用
orders_order_no_key	UNIQUE	订单号全局唯一
orders_sku_fkey	FOREIGN KEY	商品必须存在
orders_status_check	CHECK	状态只能取四个枚举值之一
orders_qty_check	CHECK	数量必须为正

第 2 步：插入商品数据

INSERT INTO products (sku, name, category, price, stock, attrs) VALUES
  ('SKU-1001', '机械键盘',     'peripherals', 299.00, 120, '{"brand": "Keychron", "switch": "red"}'),
  ('SKU-1002', '无线鼠标',     'peripherals',  59.90, 300, '{"brand": "Logitech", "dpi": 16000}'),
  ('SKU-1003', '27 寸显示器',  'display',     1299.00,  45, '{"brand": "Dell", "resolution": "2K"}'),
  ('SKU-1004', '人体工学椅',   'furniture',   1899.00,  12, '{"brand": "Herman Miller"}')
RETURNING id, sku, name;

 id |   sku    |     name
----+----------+---------------
  1 | SKU-1001 | 机械键盘
  2 | SKU-1002 | 无线鼠标
  3 | SKU-1003 | 27 寸显示器
  4 | SKU-1004 | 人体工学椅
(4 rows)

第 3 步：插入订单数据，并验证生成列自动计算

INSERT INTO orders (order_no, sku, qty, unit_price, status) VALUES
  ('SO-20260921-001', 'SKU-1001', 2, 299.00, 'paid'),
  ('SO-20260921-002', 'SKU-1002', 5,  59.90, 'paid'),
  ('SO-20260921-003', 'SKU-1003', 1, 1299.00, 'pending'),
  ('SO-20260921-004', 'SKU-1001', 1, 299.00, 'shipped');

SELECT order_no, sku, qty, unit_price, amount, status FROM orders ORDER BY order_no;

     order_no      |   sku    | qty | unit_price |  amount  | status
-------------------+----------+-----+------------+----------+---------
 SO-20260921-001   | SKU-1001 |   2 |     299.00 |   598.00 | paid
 SO-20260921-002   | SKU-1002 |   5 |      59.90 |   299.50 | paid
 SO-20260921-003   | SKU-1003 |   1 |    1299.00 |  1299.00 | pending
 SO-20260921-004   | SKU-1001 |   1 |     299.00 |   299.00 | shipped
(4 rows)

amount 列没有出现在 INSERT 的列清单里，但它被自动算出来了——这是生成列的价值。

第 4 步：用 UPSERT 处理"重复导入"

真实场景下数据导入脚本常需可重跑。用 ON CONFLICT 让脚本幂等：

INSERT INTO products (sku, name, category, price, stock, attrs) VALUES
  ('SKU-1001', '机械键盘 Pro', 'peripherals', 349.00, 150, '{"brand": "Keychron", "switch": "brown"}'),
  ('SKU-1005', 'USB-C 扩展坞', 'accessories', 199.00,  80, '{"brand": "Anker"}')
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price,
      stock = EXCLUDED.stock,
      attrs = EXCLUDED.attrs
RETURNING sku, name, price, (xmax = 0) AS is_inserted;

   sku    |     name      | price  | is_inserted
----------+---------------+--------+-------------
 SKU-1001 | 机械键盘 Pro  | 349.00 | f
 SKU-1005 | USB-C 扩展坞  | 199.00 | t
(2 rows)

(xmax = 0) 是一个常用技巧：新插入的行 xmax 为 0，被更新的行 xmax 非 0，以此区分"新增"与"更新"。

第 5 步：验证约束确实在生效

-- 试插一条违反 CHECK 的数据，应当失败
INSERT INTO orders (order_no, sku, qty, unit_price, status)
VALUES ('SO-20260921-005', 'SKU-1002', -1, 59.90, 'paid');

ERROR:  new row for relation "orders" violates check constraint "orders_qty_check"
DETAIL:  Failing row contains (5, SO-20260921-005, SKU-1002, -1, 59.90, null, paid, 2026-09-21 11:20:44.512+08).

-- 试插一条引用不存在商品的数据，应当失败
INSERT INTO orders (order_no, sku, qty, unit_price)
VALUES ('SO-20260921-006', 'SKU-9999', 1, 1.00);

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_sku_fkey"
DETAIL:  Key (sku)=(SKU-9999) is not present in table "products".

第 6 步：统计校验

SELECT
  (SELECT count(*) FROM products)                         AS product_count,
  (SELECT count(*) FROM orders)                           AS order_count,
  (SELECT sum(amount) FROM orders WHERE status = 'paid')  AS paid_amount;

 product_count | order_count | paid_amount
---------------+-------------+-------------
             5 |           4 |      897.50
(1 row)

第 7 步：清理实验数据

-- 注意删除顺序：先删子表，再删父表（或依赖 CASCADE）
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;

注意事项

• 名称长度上限 63 字节，超长会被静默截断，导致"表名看起来一样但其实是两个表"。

-- 危险：超长标识符被截断
-- CREATE TABLE this_is_an_extremely_long_table_name_that_exceeds_the_sixty_three_byte_limit (...);
-- 实际表名只保留前 63 字节

• 不要用 CHAR(n)。它按空格补齐到定长，比较时又忽略尾部空格，语义容易出错。

-- 不推荐：CHAR 会自动补空格
SELECT 'ab'::CHAR(5) AS padded, length('ab'::CHAR(5)) AS len;
-- padded = 'ab   '，len = 5

-- 推荐：需要定长校验用 TEXT + CHECK
CREATE TABLE t_code (code TEXT NOT NULL CHECK (length(code) = 5));

• NULL 与 '' 不同，NULL = NULL 不为真。判断空值只能用 IS NULL。

-- 错误！这样查不到任何行
-- SELECT * FROM users WHERE city = NULL;

-- 正确
SELECT * FROM users WHERE city IS NULL;

• UNIQUE 允许多行 NULL。若业务要求"空值也算重复"，需要用 NULLS NOT DISTINCT。

-- PostgreSQL 15+
-- PostgreSQL 15+
CREATE TABLE t_uniq (
  a INTEGER,
  b INTEGER,
  CONSTRAINT uq_ab UNIQUE NULLS NOT DISTINCT (a, b)   -- NULL 也参与唯一性判断
);

• 外键列不会被自动索引。删除父表行时，数据库需要扫描子表来检查引用，大表上会非常慢。

-- 推荐：为每个外键列显式建索引
CREATE INDEX idx_orders_sku ON orders (sku);

• 大批量插入不要逐行 INSERT。逐行提交存在大量往返与事务开销，应改用多行 VALUES 或 COPY。

-- 不推荐：逐行插入 10 万条（往返 10 万次）
-- INSERT INTO t VALUES (1); INSERT INTO t VALUES (2); ...

-- 推荐一：多行 VALUES（每批 1000 行左右）
-- INSERT INTO t (a) VALUES (1), (2), ..., (1000);

-- 推荐二（最快）：COPY
-- \copy t (a) FROM 'data.csv' WITH (FORMAT csv)

• ALTER TABLE ... ALTER COLUMN ... SET NOT NULL 需要全表扫描。大表上会长时间持锁；PostgreSQL 12+ 可先加 CHECK 约束（不阻塞）再转 NOT NULL。

-- PostgreSQL 12+ 的安全加非空流程
-- PostgreSQL 12+
ALTER TABLE big_table ADD CONSTRAINT chk_col_nn CHECK (col IS NOT NULL) NOT VALID;
ALTER TABLE big_table VALIDATE CONSTRAINT chk_col_nn;   -- 只取较弱的锁
ALTER TABLE big_table ALTER COLUMN col SET NOT NULL;    -- 可复用校验结果

• DROP TABLE 不可回退，且默认会连带删除索引与约束。有外键引用时需要 CASCADE。

-- 危险：级联删除会连带删掉引用它的外键约束
-- DROP TABLE products CASCADE;

-- 安全：不加 CASCADE，让数据库在有引用时报错提醒
DROP TABLE products;

• 别把 id 手工插入与 IDENTITY 混用，否则序列值可能与已有数据冲突。

-- 若手工插入过 id = 1000，需把序列推进到该值之后
SELECT setval(pg_get_serial_sequence('products', 'id'),
              (SELECT max(id) FROM products));

最佳实践

• 主键统一使用 BIGINT GENERATED ALWAYS AS IDENTITY，不要用 SERIAL，也不要只使用 INTEGER（易耗尽）。
• 所有列显式声明 NOT NULL 或确认可空，让"可为空"是一个主动决策而非默认状态。
• 金额、数量等精确值用 NUMERIC，绝不用 REAL / DOUBLE PRECISION。
• 时间字段统一用 TIMESTAMPTZ 并默认 now()，把时区问题在存储层消除。
• 字符串默认用 TEXT，需要长度限制时用 CHECK，不要用 VARCHAR(n) 做性能优化。
• 所有外键列都建索引，避免删除父表行时的全表扫描。
• 用 CHECK 约束把枚举取值范围固化在数据库层，而不是只写在应用代码里。
• 需要冗余计算字段时用生成列（STORED 便于建索引；18 起可选 VIRTUAL 省存储）。
• 批量导入数据一律用 COPY 或多行 VALUES，并用 ON CONFLICT 保证脚本可重跑。
• DDL 写成幂等脚本（CREATE TABLE IF NOT EXISTS），纳入版本管理，新环境可一键重建。
• 表名、列名统一小写下划线风格（order_items 而非 orderItems），避免引号与大小写问题。
• 给每个约束起有意义的名字（CONSTRAINT uq_email_tenant UNIQUE (...)），报错信息与后续 ALTER 都更清晰。
• 敏感字段不要存明文，即使有了 CHECK 约束也应在应用层加密后再写入。

下一节

继续学习：CRUD 增删改查

参考链接

• PostgreSQL 官方文档：CREATE TABLE
• PostgreSQL 官方文档：INSERT
• PostgreSQL 官方文档：数据类型
• PostgreSQL 官方文档：约束