PostgreSQL插入数据还在逐条敲？批量、冲突处理、返回自增ID的技巧你会吗？

一、INSERT语句的基本结构与核心概念

INSERT是PostgreSQL中用于向表中插入新行的核心语句，其语法框架（基于PostgreSQL 17官方文档）可拆解为以下关键部分：

[ WITH [ RECURSIVE ] with_query [, ...] ]  -- 可选：预处理临时结果集
INSERT INTO table_name [ AS alias ]       -- 目标表（可指定别名）
    [ ( column_name [, ...] ) ]           -- 可选：指定要插入的列（未指定则用所有列）
    [ OVERRIDING { SYSTEM | USER } VALUE ]-- 可选：覆盖身份列的默认值（如自增序列）
    { DEFAULT VALUES | VALUES (...) | query }  -- 插入源（默认值、值列表、查询结果）
    [ ON CONFLICT [ conflict_target ] conflict_action ]  -- 可选：处理唯一约束冲突
    [ RETURNING { * | output_expression } [, ...] ]       -- 可选：返回插入/更新的结果

二、基础插入操作：从单条到多条数据

2.1 插入单条数据（指定所有列）

当插入的列顺序与表定义完全一致时，可直接用VALUES传递所有值：

-- 假设films表结构：code(text)、title(text)、did(int)、date_prod(date)、kind(text)、len(text)
INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

2.2 插入单条数据（指定部分列）

若只插入部分列，未指定的列将使用默认值（若未定义默认值则为NULL）：

-- 省略len列（默认值为NULL）
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

2.3 使用DEFAULT值

对于希望保留默认值的列，可显式写DEFAULT（适用于任何列，包括自增序列）：

-- 方法1：在VALUES中用DEFAULT（date_prod使用默认值）
INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
-- 方法2：指定列时用DEFAULT
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');

2.4 插入多条数据

通过VALUES后追加多个括号组，可一次性插入多条数据（比多次单条插入更高效）：

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

2.5 插入全默认值的行

若所有列都使用默认值，可简化为DEFAULT VALUES：

INSERT INTO films DEFAULT VALUES;

三、进阶用法：处理复杂场景

用WITH子句预处理数据 WITH（公共表表达式，CTE）允许在插入前先定义临时结果集，适用于复杂数据过滤或转换：

-- 先从tmp_films筛选出2004年前的电影，再插入films表
WITH old_films AS (
    SELECT * FROM tmp_films WHERE date_prod < '2004-05-07'
)
INSERT INTO films SELECT * FROM old_films;

四、从查询结果插入（INSERT ... SELECT）

当需要将一个表的查询结果复制到另一个表时（比如归档历史数据、拆分大表），INSERT ... SELECT是最佳选择。

4.1 场景：数据归档

假设我们要把users表中2023年之前注册的用户归档到archived_users表：

-- 1. 创建归档表（结构与users一致）
CREATE TABLE archived_users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP
);

-- 2. 插入查询结果（筛选2023年前的用户）
INSERT INTO archived_users (id, name, email, phone, created_at)
SELECT id, name, email, phone, created_at
FROM users
WHERE created_at < '2023-01-01 00:00:00';

注意事项：

• 列顺序匹配：SELECT的列顺序必须和INSERT INTO的列顺序一致（或明确指定列名）。
• 性能：如果数据量很大，建议分批次插入（比如每次插1000条），避免锁表。

五、处理插入冲突（ON CONFLICT）

在实际开发中，唯一约束冲突是高频问题（比如用户注册时邮箱已存在）。PostgreSQL提供ON CONFLICT子句，允许我们优雅处理冲突，而不是直接报错。

5.1 冲突处理的两种策略

ON CONFLICT支持两种核心策略：

1. DO NOTHING：忽略冲突的记录（不插入、不报错）。
2. DO UPDATE：更新现有记录（用新值覆盖旧值）。

场景1：忽略重复数据（DO NOTHING）

导入产品数据时，跳过已存在的产品名称：

-- 创建products表（product_name唯一）
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) UNIQUE NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0
);

-- 插入产品，重复名称则忽略
INSERT INTO products (product_name, price, stock)
VALUES ('Laptop', 999.99, 10), ('Phone', 599.99, 20)
ON CONFLICT (product_name) DO NOTHING;

场景2：更新现有记录（DO UPDATE）

用户注册时，若邮箱已存在，则更新昵称和最后登录时间：

-- 给users表添加last_login_at列（默认当前时间）
ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 插入或更新用户
INSERT INTO users (name, email, last_login_at)
VALUES ('Alice Smith', '[email protected]', CURRENT_TIMESTAMP)
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name,  -- 用新昵称更新旧昵称
    last_login_at = EXCLUDED.last_login_at;  -- 用新登录时间更新旧时间

关键概念：EXCLUDED
EXCLUDED是PostgreSQL的“临时变量”，保存了你原本要插入的那条记录的值。比如EXCLUDED.name就是这次要插入的Alice的新昵称，而users.name是数据库中已有的旧昵称。

场景3：复杂更新（库存累加）

产品重复时，现有库存 + 新库存（适合库存管理）：

-- 插入产品，重复时累加库存
INSERT INTO products (product_name, price, stock)
VALUES ('Laptop', 999.99, 10), ('Phone', 599.99, 20)
ON CONFLICT (product_name) DO UPDATE
SET stock = products.stock + EXCLUDED.stock;  -- 现有库存 + 新库存

六、插入后返回结果（RETURNING子句）

插入数据后，常常需要立即获取自动生成的ID（比如用户ID），用于后续操作（如创建用户Profile）。RETURNING子句可以直接返回插入的记录的指定列，无需再查一次数据库。

6.1 场景：获取自增ID

插入用户后，返回id用于创建Profile：

-- 插入用户并返回id
INSERT INTO users (name, email)
VALUES ('Jack Wilson', '[email protected]')
RETURNING id;

执行结果：

 id  
-----
 1001
(1 row)

用返回的id创建Profile：

-- 创建user_profiles表
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY REFERENCES users(id),  -- 关联users表的id
    bio TEXT,
    avatar_url VARCHAR(200)
);

-- 用返回的id插入Profile
INSERT INTO user_profiles (user_id, bio, avatar_url)
VALUES (1001, 'Hi, I''m Jack!', 'https://example.com/avatars/jack.png');

6.2 返回多列

RETURNING可以返回多个列（比如id和created_at）：

-- 返回id和created_at
INSERT INTO users (name, email)
VALUES ('Kelly Moore', '[email protected]')
RETURNING id, created_at;

执行结果：

 id  |          created_at
-----+-------------------------------
 1002 | 2024-05-20 14:30:00.123456
(1 row)

七、插入流程的可视化

为了更清晰理解插入的逻辑，我们用流程图总结完整插入流程：

flowchart LR
A[开始] --> B[编写INSERT语句]
B --> C[验证列与值的匹配性]
C --> D{是否有默认值?}
D -->|是| E[填充默认值]
D -->|否| F[使用指定值]
E --> G
F --> G
G{是否有冲突?}
G -->|是| H[执行ON CONFLICT策略]
G -->|否| I[执行插入]
H --> I
I --> J{是否有RETURNING?}
J -->|是| K[返回指定列值]
J -->|否| L[结束]
K --> L

八、课后Quiz

问题1（冲突处理）

假设orders表有order_id（自增主键）、user_id（关联users.id）、order_no（唯一约束，订单号）、total_amount。现在要插入一批订单，若order_no已存在，则更新总金额为现有金额 + 新金额。请写出INSERT语句。

答案解析

INSERT INTO orders (user_id, order_no, total_amount)
VALUES (1001, '20240520001', 199.99), (1002, '20240520002', 299.99)
ON CONFLICT (order_no) DO UPDATE
SET total_amount = orders.total_amount + EXCLUDED.total_amount;

问题2（INSERT ... SELECT）

将orders表中2024年5月的订单复制到may_2024_orders表（结构与orders一致），请写出SQL语句。

答案解析

-- 创建may_2024_orders表（结构与orders一致）
CREATE TABLE may_2024_orders (
    order_id INT PRIMARY KEY,
    user_id INT REFERENCES users(id),
    order_no VARCHAR(50) UNIQUE NOT NULL,
    total_amount NUMERIC(10,2) NOT NULL
);

-- 插入5月的订单
INSERT INTO may_2024_orders (order_id, user_id, order_no, total_amount)
SELECT order_id, user_id, order_no, total_amount
FROM orders
WHERE order_time BETWEEN '2024-05-01 00:00:00' AND '2024-05-31 23:59:59';

九、常见报错及解决方案

插入数据时，以下报错最常见，我们逐一分析原因和解决办法：

报错1：ERROR: duplicate key value violates unique constraint "users_email_key"

原因：插入了重复的唯一键值（比如email已存在）。
解决办法：

1. 用ON CONFLICT处理（DO NOTHING或DO UPDATE）。
2. 插入前检查数据是否重复（应用层或SQL查询）。
   预防建议：在应用层做唯一性校验（比如注册时提示“邮箱已存在”）。

报错2：ERROR: null value in column "name" violates not-null constraint

原因：name列有NOT NULL约束，但插入时未提供值。
解决办法：

1. 确保插入时填写name的值。
2. 若name可默认，修改表结构添加DEFAULT（如DEFAULT 'Unknown'）。
   预防建议：应用层做必填字段校验（比如前端表单必填）。

报错3：ERROR: column "created_at" is of type timestamp but expression is of type integer

原因：created_at是TIMESTAMP类型，但插入了整数（比如时间戳1620000000）。
解决办法：用TO_TIMESTAMP函数将整数转为TIMESTAMP：

-- 将时间戳转为TIMESTAMP
INSERT INTO users (name, email, created_at)
VALUES ('Frank', '[email protected]', TO_TIMESTAMP(1620000000));

九、参考链接

• PostgreSQL官方INSERT语句文档：www.postgresql.org/docs/17/sql…
• ON CONFLICT子句文档：www.postgresql.org/docs/17/sql…
• RETURNING子句文档：www.postgresql.org/docs/17/sql…