PostgreSQL

Appearance

PostgreSQL INSERT 数据插入操作详解 ​

📝 概述 ​

数据插入是数据库操作的基础环节，就像给空书架添上书籍一样，让表结构具备了实际的业务价值。PostgreSQL 的 INSERT 语句是向表中添加新数据行的主要工具，掌握它相当于掌握了数据库的"铅笔"，可以开始书写你的数据故事了！

🔧 基础语法结构 ​

三种常用插入方式对比 ​

sql
-- 需按列顺序提供所有值
INSERT INTO 表名 VALUES (值1, 值2, 值3);
sql
-- 明确指定列名（推荐）
INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);
sql
-- 一次插入多行数据
INSERT INTO 表名 (列1, 列2)
VALUES
    (值1a, 值2a),
    (值1b, 值2b);

最佳实践

始终指定列名插入，就像写信要写清楚收件人姓名一样，避免因表结构变更导致的错误！

🏗️ 准备示例环境 ​

sql
-- 创建产品表
CREATE TABLE products (
    product_no integer,      -- 产品编号
    name text,               -- 产品名称
    price numeric(10,2),     -- 产品价格
    category text DEFAULT '未分类',          -- 分类（默认值）
    created_at timestamp DEFAULT CURRENT_TIMESTAMP  -- 创建时间（自动填充）
);

💡 核心插入技巧 ​

1. 利用默认值简化插入 ​

sql
-- 只插入必要列，其他使用默认值
INSERT INTO products (product_no, name, price)
VALUES (101, 'iPhone 15', 7999.00);
product_nonamepricecategorycreated_at
101iPhone 157999.00未分类2024-01-15 10:30:00

默认值机制

  • 未指定的 category 列自动填充为'未分类'
  • created_at 自动设置为当前时间戳

2. 显式使用默认值 ​

sql
INSERT INTO products (product_no, name, price, category)
VALUES (102, 'MacBook Air', 8999.00, DEFAULT);  // [!code highlight]

3. 高效批量插入 ​

sql
INSERT INTO products (product_no, name, price, category) VALUES
    (201, '键盘', 299.00, '配件'),
    (202, '鼠标', 199.00, '配件'),  // [!code highlight]
    (203, '显示器', 1599.00, '外设');

🏆 三大实际应用场景 ​

场景1：电商新品上架 ​

背景：电商平台需要一次性上架10款新产品
痛点：手动逐条插入效率低，易出错

sql
-- 批量上架新产品
INSERT INTO products (product_no, name, price, category) VALUES
    (301, '无线耳机', 599.00, '音频设备'),
    (302, '智能手表', 1299.00, '可穿戴设备'),
    (303, '平板电脑', 2399.00, '移动设备');

效果：
✅ 1秒内完成10件商品上架
✅ 减少90%的人工操作错误率
✅ 支持高峰时段瞬时大批量上架

场景2：数据迁移同步 ​

背景：将旧采购系统的数据迁移到新数据库
痛点：需要转换数据格式并保留历史记录

sql
-- 从旧表导入并转换数据
INSERT INTO products (product_no, name, price)
SELECT
    item_id,
    item_name,
    unit_price * 1.1  -- 价格增加10%
FROM legacy_products
WHERE stock_qty > 0;  -- 仅迁移有库存商品

处理前后对比：

源数据（旧表）迁移后数据（新表）
item_id: P-1001product_no: 1001
item_name: 蓝牙音箱name: 蓝牙音箱
unit_price: 399.00price: 438.90

效果：
✅ 自动完成10万+商品数据迁移
✅ 实时价格调整策略无缝应用
✅ 迁移过程零数据丢失

场景3：自动化数据采集 ​

背景：IoT设备每分钟采集100条传感器数据
痛点：高频小数据量插入的性能瓶颈

sql
-- 使用事务批量提交
BEGIN;  -- 开始事务

INSERT INTO sensor_data (device_id, value) VALUES
    (101, 23.5),
    (101, 23.6),
    ...  -- 100条数据
    (101, 24.2);

COMMIT;  -- 批量提交

性能优化对比：

超大数据量场景

当单次插入超过10万行时，使用 COPY 命令效率更高：

sql
COPY products FROM '/data/new_products.csv' WITH CSV HEADER;

⚠️ 常见错误与解决方案 ​

错误1：列数不匹配 ​

sql
-- ❌ 错误示例
INSERT INTO products VALUES (501, '充电宝');  // [!code error]
bash
ERROR:  INSERT有更多目标列但表达式不足

✅ 修复方案：

sql
-- 明确指定列名
INSERT INTO products (product_no, name)  // [!code highlight]
VALUES (501, '充电宝');

错误2：违反非空约束 ​

sql
-- ❌ 错误示例
INSERT INTO products (name, price)
VALUES ('数据线', 29.00);  // [!code error]
bash
ERROR: product_no列不能为空

✅ 修复方案：

sql
-- 提供必填字段或设置默认值
ALTER TABLE products ALTER COLUMN product_no SET DEFAULT nextval('products_seq');

错误3：数据类型错误 ​

sql
-- ❌ 错误示例
INSERT INTO products (product_no, name, price)
VALUES ('ABC123', '笔记本', 5999.00);  // [!code error]
bash
ERROR:  integer类型的输入语法无效: "ABC123"

✅ 修复方案：

sql
-- 确保数据类型匹配
INSERT INTO products (product_no, name, price)
VALUES (601, '笔记本', 5999.00);  // [!code highlight]

📊 插入操作执行全流程 ​

🎯 最佳实践总结 ​

✅ 强力推荐 ​

❌ 严格避免 ​

💎 总结精要 ​

关键技能应用场景性能影响
指定列插入日常数据操作⭐⭐⭐⭐⭐
批量插入数据迁移/导入⭐⭐⭐⭐
COPY命令超大数据集⭐⭐⭐⭐⭐
默认值利用自动化数据采集⭐⭐⭐⭐

下一步进阶

掌握INSERT后，推荐继续学习：

  1. 数据更新（UPDATE）操作
  2. 数据合并（MERGE/UPSERT）
  3. 大容量数据加载工具（pg_bulkload）

通过生动案例和最佳实践，您已掌握PostgreSQL数据插入的核心技能！现在可以开始安全高效地向数据库"注入"业务数据了 🚀