PostgreSQL INSERT INTO 语句详解
在 PostgreSQL 数据库的日常开发中，INSERT INTO 是最基础也最常用的操作之一。它负责将新记录写入表中，支持单行、多行、部分字段插入等多种灵活用法。理解其语法细节和最佳实践，能显著提升数据写入的效率和稳定性。
基本语法结构
标准的插入语句结构如下：
INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);
其中 table_name 是目标表，括号内列出要插入的字段，VALUES 后紧跟对应的值列表。如果省略字段列表，则必须为所有列提供值且顺序一致。
常见插入场景示例
1. 创建示例表
为了演示方便，我们先定义一个简单的员工表：
CREATE TABLE company (
id INT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INT NOT NULL,
address CHAR(50),
salary REAL,
join_date DATE
);
2. 单行完整插入
当需要插入一条包含所有字段数据的记录时：
INSERT INTO company VALUES (1, 'Paul', 32, 'California', 20000.00, '2001-07-13');
注意这里没有指定列名，因此值的顺序必须与建表时的列顺序严格匹配，否则容易出错。
3. 指定字段插入（部分字段）
实际开发中，我们往往只关心核心业务字段，或者某些字段有默认值。此时可以显式指定列名：
INSERT INTO company (id, name, age, address, join_date)
VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');
这种方式更稳健，即使后续表结构变更（如增加中间列），只要不改变现有列的顺序或名称，SQL 依然有效。
4. 使用 DEFAULT 值插入
如果某列定义了默认值，可以在插入时显式使用 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 可忽略冲突而不报错。
插入性能优化
在生产环境中，数据写入的性能至关重要。以下是几个关键优化点：
- 批量插入：尽量合并多条
INSERT语句，减少事务提交频率。 - 事务包装：将多个插入操作放入同一个事务块中，保证原子性并降低日志开销。
- 禁用索引：在进行海量数据导入前，临时禁用非唯一索引，导入完成后再重建，速度会有数量级提升。
- 使用 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。
最佳实践建议
在实际项目中，建议遵循以下规范：
- 明确指定列名：不要依赖隐式的列顺序，防止表结构变更导致 SQL 失效。
- 参数化查询：在代码中拼接 SQL 时务必使用预编译语句，防止 SQL 注入攻击。
- 批量操作：循环插入不如批量插入高效，尽量聚合数据后统一写入。
- 错误处理：始终检查执行结果，捕获异常并记录日志。
- 权限控制：遵循最小权限原则，只授予必要的
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 语句及其变体，可以构建高效可靠的数据写入流程，为应用系统提供坚实的数据存储基础。