24 INSERT

INSERT 会向表中添加行。基本形式是 INSERT INTO table (columns) VALUES (values)。你可以插入一行、在一条语句中插入多行，或插入 SELECT 的结果。通过单条 INSERT ... VALUES (...), (...), ... 进行批量插入，比执行成千上万条独立语句快得多，因为后者每条语句都要承担一次网络往返和一次重新规划的代价。

本章要点

• 单行和多行 INSERT
• 为什么明确列出列名是正确的默认做法
• 从查询插入（INSERT INTO ... SELECT）
• 使用 DEFAULT 并省略列
• 状态消息：INSERT 0 5 究竟意味着什么

示例

-- One row at a time (most explicit, often fine for app code)
INSERT INTO fruits (id, name) VALUES (1, 'apple');
INSERT INTO fruits (id, name) VALUES (2, 'banana');
INSERT INTO fruits (id, name) VALUES (3, 'cherry');

-- Many rows in one statement (much faster for bulk loads)
INSERT INTO fruits (id, name) VALUES
  (4, 'date'),
  (5, 'elderberry');

-- INSERT ... SELECT: copy rows from another query
-- (here a generator, to avoid duplicating fruits we just inserted)
INSERT INTO fruits (id, name)
SELECT n + 100, 'fruit_' || n
FROM generate_series(1, 3) AS n;

-- Confirm
SELECT count(*) AS row_count FROM fruits;

在 create-table.sql 之后运行此脚本——表必须已经存在。前 3 条语句各插入一行；第 4 条插入两行；第 5 条通过 generate_series 生成并插入三行。psql 输出的状态消息 INSERT 0 5 意味着已插入，旧 OID 为 0，影响 5 行——OID 属于遗留机制，可以忽略第一个数字；第二个数字才是行数。

为什么要列出列名？有两个原因。第一，如果表后来增加一列，那么你的 INSERT INTO fruits VALUES (...)（不带列清单）会失败，而 INSERT INTO fruits (id, name) VALUES (...) 仍然可以工作。译注：这里的“会失败”是原文的概括，并非对所有新增列情形都成立。PostgreSQL 在省略列清单且只提供 N 个值时，会按声明顺序将这些值映射到前 N 列，省略的列使用默认值或 NULL；因此，新增到末尾且可以省略的列未必导致失败，最终还取决于默认值和约束。第二，列出列名能让任何阅读代码的人都清楚意图——值不再是靠位置猜测的谜题。

从文件（CSV 等）批量装载时，优先使用 COPY（后文会介绍）——它的速度明显快于 INSERT。应用代码应将行批量组织到多行 INSERT 语句中，或使用驱动程序级的预备语句。

运行示例

运行方式：

$ psql -f source/insert.sql postgres
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 2
INSERT 0 3
 row_count
-----------
         8
(1 row)

常见陷阱

• 表中已有行时重复运行脚本，会导致主键或唯一约束冲突（添加主键后）。解决办法取决于意图：删除并重新创建表、使用 TRUNCATE，或者使用 INSERT ... ON CONFLICT 执行插入或更新（UPSERT），详见下一课。
• 按位置执行 INSERT 时列顺序错误，会悄悄破坏数据——只要类型兼容，数据库就会把错误的值插入错误的列。始终列出列名。
• 在生产环境中，使用错误时区插入时间戳，是“时间差了 8 小时”的最常见原因。使用 timestamptz，让 PostgreSQL 处理转换。

下载

SQL 文件：insert.sql · 全部示例文件 · 准备工作