数据插入

📝 概述

数据插入是数据库操作的基础环节，它让空的表结构具备了实际的业务价值。在 PostgreSQL 中，INSERT 语句是向表中添加新数据行的主要工具。无论是单行插入还是批量插入，掌握正确的插入技巧对于数据库应用开发至关重要。

核心概念数据插入遵循"行级完整性"原则：每次插入操作必须创建完整的数据行，即使某些列的值未知，也需要为这些列提供默认值或 NULL 值。

🎯 学习目标

通过本章学习，您将掌握：

• 基本的 INSERT 语句语法和使用方法
• 多种数据插入策略和最佳实践
• 如何处理默认值和部分列插入
• 批量插入和查询结果插入技巧
• 常见错误的诊断和解决方案

🔧 基础语法结构

INSERT 语句基本形式

语法模板

sql

INSERT INTO table_name VALUES (value1, value2, value3, ...);

sql

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

sql

INSERT INTO table_name (column1, column2, column3, ...)
VALUES
    (value1a, value2a, value3a, ...),
    (value1b, value2b, value3b, ...),
    (value1c, value2c, value3c, ...);

🏗️ 准备示例环境

在开始学习之前，让我们创建一个示例表来演示各种插入操作：

-- 创建产品表
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric(10,2),
    category text DEFAULT '未分类',
    created_at timestamp DEFAULT CURRENT_TIMESTAMP
);

表结构说明：

列名	数据类型	约束	说明
product_no	integer	-	产品编号
name	text	-	产品名称
price	numeric(10,2)	-	产品价格（精确到分）
category	text	DEFAULT '未分类'	产品分类（有默认值）
created_at	timestamp	DEFAULT CURRENT_TIMESTAMP	创建时间（自动填充）

💡 基本插入操作

1. 完整行插入

当您知道所有列的值时，可以使用最简单的插入方式：

-- 按表定义的列顺序插入数据
INSERT INTO products VALUES (1, '奶酪', 9.99, '乳制品', '2024-01-15 10:30:00');

执行结果：

INSERT 0 1

使用这种方式时，**必须严格按照表中列的定义顺序**提供所有列的值，包括有默认值的列。

2. 指定列名插入（推荐方式）

为了提高代码的可读性和维护性，建议始终明确指定列名：

-- 明确指定列名和对应的值
INSERT INTO products (product_no, name, price, category)
VALUES (2, '面包', 1.99, '烘焙食品');

优势对比：

方式	优点	缺点	推荐度
不指定列名	语法简洁	依赖列顺序，维护困难	⭐⭐
指定列名	清晰明确，易维护	语法稍长	⭐⭐⭐⭐⭐

3. 灵活的列顺序

指定列名的另一个好处是可以任意调整列的顺序：

-- 列的顺序可以与表定义不同
INSERT INTO products (name, price, product_no, category)
VALUES ('牛奶', 2.99, 3, '乳制品');

🎯 部分列插入与默认值

1. 省略部分列

当某些列有默认值或允许 NULL 时，可以只插入部分列的数据：

-- 只插入必要的列，其他列使用默认值
INSERT INTO products (product_no, name, price)
VALUES (4, '苹果', 3.50);

插入后的数据状态：

product_no	name	price	category	created_at
4	苹果	3.50	未分类	2024-01-15 10:35:22

默认值机制

• category 列自动填充为默认值 '未分类'
• created_at 列自动填充为当前时间戳

2. 显式使用默认值

您也可以在插入时明确指定使用默认值：

-- 显式请求使用默认值
INSERT INTO products (product_no, name, price, category)
VALUES (5, '橙子', 4.20, DEFAULT);

3. 插入完全默认的行

PostgreSQL 允许插入一行全部使用默认值的数据：

-- 所有列都使用默认值（需要所有列都有默认值或允许NULL）
INSERT INTO products DEFAULT VALUES;

这种方式要求表中所有没有默认值的列都允许 NULL，否则会报错。

🚀 批量插入操作

1. 多行 VALUES 插入

当需要一次性插入多条记录时，使用多行 VALUES 是最高效的方式：

-- 一次插入多行数据
INSERT INTO products (product_no, name, price, category) VALUES
    (6, '香蕉', 2.30, '水果'),
    (7, '葡萄', 8.90, '水果'),
    (8, '西红柿', 3.40, '蔬菜');

执行结果：

INSERT 0 3

性能对比：

2. 从查询结果插入

将查询结果直接插入到表中是数据迁移和处理的常用技巧：

-- 从其他表或查询结果插入数据
INSERT INTO products (product_no, name, price, category)
SELECT product_id, product_name, unit_price, 'imported'
FROM temp_products WHERE import_date = CURRENT_DATE;

应用场景：

• 数据迁移和同步
• 备份表数据
• 数据转换和清洗
• 报表数据生成

📊 实际业务示例

示例 1：电商商品录入

假设我们正在为一个电商平台录入新商品：

-- 电商商品批量录入
INSERT INTO products (product_no, name, price, category) VALUES
    (101, 'iPhone 15 Pro', 7999.00, '电子产品'),
    (102, '小米13', 3999.00, '电子产品'),
    (103, 'MacBook Air', 8999.00, '电子产品'),
    (104, '索尼耳机', 1299.00, '数码配件');

示例 2：库存补充记录

-- 从采购清单补充库存
INSERT INTO products (product_no, name, price)
SELECT sku_code, item_name, purchase_price FROM purchase_list
WHERE delivery_date = '2024-01-15' AND status = 'confirmed';

⚠️ 常见错误与解决方案

1. 列数不匹配错误

-- ❌ 错误示例：列数不匹配
INSERT INTO products VALUES (1, '测试商品');

错误信息：

ERROR: INSERT has more target columns than expressions

✅ 正确做法：

-- 明确指定要插入的列
INSERT INTO products (product_no, name) VALUES (1, '测试商品');

2. 数据类型不匹配

-- ❌ 错误示例：数据类型错误
INSERT INTO products (product_no, name, price) VALUES ('abc', '商品', 9.99);

错误信息：

ERROR: invalid input syntax for type integer: "abc"

✅ 正确做法：

-- 确保数据类型匹配
INSERT INTO products (product_no, name, price) VALUES (1, '商品', 9.99);

3. 违反约束条件

-- ❌ 错误示例：违反NOT NULL约束
INSERT INTO products (name) VALUES ('商品');

约束检查插入数据时，PostgreSQL 会自动检查所有定义的约束条件，包括：

• NOT NULL 约束
• 主键约束
• 外键约束
• 检查约束
• 唯一性约束

🔍 插入操作的执行流程

📈 性能优化建议

1. 批量插入优化

sql

-- 一次插入多行（推荐）
INSERT INTO products (product_no, name, price) VALUES
    (1, '商品A', 10.00),
    (2, '商品B', 20.00),
    (3, '商品C', 30.00);

sql

-- 多次单行插入（避免）
INSERT INTO products (product_no, name, price) VALUES (1, '商品A', 10.00);
INSERT INTO products (product_no, name, price) VALUES (2, '商品B', 20.00);
INSERT INTO products (product_no, name, price) VALUES (3, '商品C', 30.00);

2. 事务控制

BEGIN;
INSERT INTO products (product_no, name, price) VALUES
    (201, '批量商品1', 15.00),
    (202, '批量商品2', 25.00),
    (203, '批量商品3', 35.00);
-- 其他相关操作...
COMMIT;

3. 大量数据插入

对于大量数据插入，考虑使用 COPY 命令：

-- 从CSV文件批量导入数据
COPY products(product_no, name, price, category)
FROM '/path/to/products.csv'
WITH (FORMAT csv, HEADER true);

🎯 最佳实践总结

✅ 推荐做法

1. 始终指定列名：提高代码可读性和维护性
2. 使用批量插入：提高性能，减少网络开销
3. 合理使用默认值：简化插入操作
4. 事务控制：确保数据一致性
5. 错误处理：预测和处理可能的错误情况

❌ 避免做法

1. 硬编码列顺序：增加维护成本
2. 逐行插入大量数据：性能低下
3. 忽略约束检查：可能导致数据不一致
4. 不使用事务：数据操作缺乏原子性

🔧 实战练习

练习 1：基础插入操作

创建一个学生表并插入数据：

-- 创建学生表
CREATE TABLE students (
    student_id integer,
    name varchar(50),
    age integer,
    grade varchar(10) DEFAULT 'A',
    enrollment_date date DEFAULT CURRENT_DATE
);

-- 插入学生数据
INSERT INTO students (student_id, name, age) VALUES
    (1, '张三', 20),
    (2, '李四', 19),
    (3, '王五', 21);

练习 2：从查询结果插入

-- 创建优秀学生表并从学生表中筛选数据
CREATE TABLE honor_students AS
SELECT student_id, name, age
FROM students WHERE grade = 'A';

查看答案练习 1 将创建 3 条学生记录，其中 grade 列将自动填充为默认值 'A'，enrollment_date 将填充为当前日期。

练习 2 将创建一个新表并复制所有成绩为 'A' 的学生数据。

📚 本章小结

通过本章的学习，我们全面掌握了 PostgreSQL 中的数据插入操作：

• 基础语法：掌握了 INSERT 语句的各种形式和用法
• 灵活插入：学会了处理默认值和部分列插入
• 批量操作：了解了高效的批量插入技巧
• 最佳实践：建立了良好的编码习惯和性能意识

数据插入是数据库操作的基础，熟练掌握这些技巧将为后续的数据查询、更新和删除操作打下坚实的基础。

下一步学习在掌握了数据插入之后，建议继续学习：

• 数据更新（UPDATE）操作
• 数据删除（DELETE）操作
• 数据合并（MERGE）操作