PostgreSQL INSERT INTO 语句详解

一、INSERT INTO 语句概述

PostgreSQL 的 INSERT INTO 语句是数据库操作中最基础且最重要的命令之一，用于向表中添加新记录。该语句支持单行插入、多行插入、部分字段插入等多种灵活用法。

基本语法结构

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

二、语法元素详解

三、插入数据完整示例

1. 创建示例表

CREATE TABLE ***pany(
   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 ***pany 
VALUES (1, 'Paul', 32, 'California', 20000.00, '2001-07-13');

3. 指定字段插入（部分字段）

INSERT INTO ***pany (id, name, age, address, join_date)
VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');

4. 使用DEFAULT值插入

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

5. 多行批量插入

INSERT INTO ***pany (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操作流程图

五、数据插入架构图

六、高级插入技巧

1. 从查询结果插入

INSERT INTO ***pany_backup
SELECT * FROM ***pany WHERE age > 25;

2. 使用RETURNING子句

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

3. ON CONFLICT处理冲突

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

七、插入性能优化

1. 批量插入：多行VALUES优于多个单行INSERT
2. 事务包装：将多个INSERT放入一个事务
3. 禁用索引：大数据量导入时临时禁用索引
4. 使用COPY：大数据量使用COPY命令替代INSERT

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

八、错误处理方案

1. 主键冲突

错误：duplicate key value violates unique constraint
解决：

INSERT INTO ***pany (id, name) 
VALUES (1, 'Paul') 
ON CONFLICT (id) DO NOTHING;

2. 非空约束

错误：null value in column "name" violates not-null constraint
解决：确保必填字段都有值

3. 数据类型不匹配

错误：column "age" is of type integer but expression is of type text
解决：确保值类型与列定义匹配

九、插入结果解析

PostgreSQL返回的插入结果格式：

• INSERT 0 1：成功插入1行
• INSERT oid 1：插入单行并返回OID
• INSERT 0 0：ON CONFLICT DO NOTHING时可能返回

十、最佳实践建议

1. 明确指定列名：避免表结构变更导致的问题
2. 参数化查询：防止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;

十二、总结

PostgreSQL的INSERT INTO语句提供了灵活多样的数据插入方式，掌握其各种用法和优化技巧对于高效数据库操作至关重要。关键点包括：

1. 理解基本语法和变体形式
2. 掌握批量插入提高效率
3. 学会处理插入冲突
4. 遵循安全最佳实践
5. 了解性能优化方法

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