PostgreSQL INSERT INTO 语句详解

如果某列定义了默认值，可以在插入时显式使用 DEFAULT 关键字：

INSERT INTO company (id, name, age, address, salary, join_date)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT);

这比手动计算默认值更安全，也能避免硬编码带来的维护成本。

5. 多行批量插入

一次性插入多条数据时，PostgreSQL 支持在 VALUES 后追加多个元组：

INSERT INTO company (id, name, age, address, salary, join_date)
VALUES 
    (4, 'Mark', 25, 'Rich-Mond', 65000.00, '2007-12-13'),
    (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');

相比执行多次单条 INSERT，批量插入能减少网络往返次数，大幅提升吞吐量。

高级插入技巧

从查询结果插入

除了直接写值，还可以直接将查询结果写入新表：

INSERT INTO company_backup SELECT * FROM company WHERE age > 25;

这在数据迁移或备份场景中非常实用。

使用 RETURNING 子句

插入后如果需要立即获取生成的自增 ID 或其他信息，可以使用 RETURNING：

INSERT INTO company (id, name, age) VALUES (6, 'Lisa', 29)
RETURNING id, name;

这样应用层无需再额外查询一次，减少了交互开销。

ON CONFLICT 处理冲突

在高并发或重复导入场景下，主键冲突不可避免。PostgreSQL 提供了优雅的冲突解决机制：

INSERT INTO company (id, name, age) VALUES (1, 'Paul', 33)
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age;

这条语句的意思是：如果 id 冲突，则更新该行的 name 和 age；如果不冲突，则正常插入。配合 DO NOTHING 可忽略冲突而不报错。

插入性能优化

在生产环境中，数据写入的性能至关重要。以下是几个关键优化点：

1. 批量插入：尽量合并多条 INSERT 语句，减少事务提交频率。
2. 事务包装：将多个插入操作放入同一个事务块中，保证原子性并降低日志开销。
3. 禁用索引：在进行海量数据导入前，临时禁用非唯一索引，导入完成后再重建，速度会有数量级提升。
4. 使用 COPY 命令：对于超大数据量导入，COPY 命令比 INSERT 快得多。

-- 使用 COPY 命令导入 CSV 文件
COPY company FROM '/path/to/data.csv' DELIMITER ',' CSV;

错误处理方案

常见的插入错误通常源于约束校验失败，掌握这些错误信息有助于快速定位问题：

• 主键冲突：错误提示 duplicate key value violates unique constraint。解决方案是使用 ON CONFLICT 子句。
• 非空约束：错误提示 null value in column ... violates not-null constraint。需确保必填字段都有值。
• 数据类型不匹配：错误提示 column "age" is of type integer but expression is of type text。检查传入值的类型是否与列定义一致。

插入结果解析

执行成功后，PostgreSQL 会返回类似 INSERT 0 1 的结果，表示成功插入了 1 行数据。如果是 INSERT oid 1，则表示插入了单行并返回了 OID（旧版本特性）。在使用 ON CONFLICT DO NOTHING 时，若发生冲突未执行插入，可能返回 INSERT 0 0。

最佳实践建议

在实际项目中，建议遵循以下规范：

1. 明确指定列名：不要依赖隐式的列顺序，防止表结构变更导致 SQL 失效。
2. 参数化查询：在代码中拼接 SQL 时务必使用预编译语句，防止 SQL 注入攻击。
3. 批量操作：循环插入不如批量插入高效，尽量聚合数据后统一写入。
4. 错误处理：始终检查执行结果，捕获异常并记录日志。
5. 权限控制：遵循最小权限原则，只授予必要的 INSERT 权限。

完整示例演示

下面是一个完整的测试流程，涵盖建表、单行/多行插入及查询验证：

-- 创建测试表
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    hire_date DATE DEFAULT CURRENT_DATE
);

-- 单行插入
INSERT INTO employees (emp_name, department, salary)
VALUES ('张三', '技术部', 15000.00);

-- 多行插入
INSERT INTO employees (emp_name, department, salary)
VALUES 
    ('李四', '市场部', 12000.00),
    ('王五', '财务部', 18000.00),
    ('赵六', '技术部', 16000.00);

-- 带返回的插入
INSERT INTO employees (emp_name, department, salary)
VALUES ('钱七', '人事部', 14000.00)
RETURNING emp_id, emp_name;

-- 查询结果
SELECT * FROM employees;

通过合理应用 INSERT 语句及其变体，可以构建高效可靠的数据写入流程，为应用系统提供坚实的数据存储基础。