35、PostgreSQL数据操作：INSERT、SELECT、UPDATE、DELETE、COPY命令批量导入

说实话，PostgreSQL 的增删改查，看起来跟其他数据库差不多。但真正用起来，你会发现它有很多「小脾气」。我最早从 MySQL 转过来时，就被它的 COPY 命令惊艳到了——批量导入速度简直起飞。

今天咱们就把 INSERT、SELECT、UPDATE、DELETE 这四大金刚，再加上 COPY 这个批量神器，一次性讲透。嗯，都是我在项目中踩过的坑，你直接拿去用就行。

35.1 INSERT：插入数据的几种姿势

最基本的插入，大家都会。但 PG 有几个特色写法，我建议你记住。

-- 标准插入
INSERT INTO users (name, age, email) 
VALUES ('张三', 28, 'zhangsan@example.com');

-- 一次插入多行（PG 支持得很棒）
INSERT INTO users (name, age, email) VALUES
('李四', 32, 'lisi@example.com'),
('王五', 25, 'wangwu@example.com'),
('赵六', 30, 'zhaoliu@example.com');

-- 带 RETURNING 子句（这个很实用）
INSERT INTO users (name, age, email) 
VALUES ('陈七', 27, 'chenqi@example.com')
RETURNING id, created_at;

你想想看，如果不用 RETURNING，你得先 INSERT，再 SELECT max(id)，多线程下还可能拿错。何必呢？

35.2 SELECT：查询不只是 SELECT *

SELECT 是日常用得最多的操作。我见过不少新手上来就 SELECT *，其实这样不好。

-- 明确指定字段（推荐）
SELECT id, name, age FROM users WHERE age > 25;

-- 带 LIMIT 和 OFFSET 分页
SELECT id, name, age FROM users 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 20;

-- 聚合查询
SELECT age, COUNT(*) as cnt 
FROM users 
GROUP BY age 
HAVING COUNT(*) > 1;

为什么会这样？因为 OFFSET 会把前面的行全部扫一遍，跳过去。说白了就是白干活。

35.3 UPDATE：更新数据要小心

UPDATE 看起来简单，但稍不注意就会出大问题。我吃过亏，所以现在写 UPDATE 必加 WHERE。

-- 单表更新
UPDATE users 
SET age = 29, updated_at = NOW()
WHERE id = 1001;

-- 带 FROM 子句的关联更新
UPDATE users u
SET age = u.age + 1
FROM orders o
WHERE u.id = o.user_id 
  AND o.total > 1000;

35.4 DELETE：删除的艺术

DELETE 比 UPDATE 更危险。因为数据一旦删了，想恢复就难了。

-- 普通删除
DELETE FROM users WHERE id = 1001;

-- 带 USING 的关联删除
DELETE FROM users u
USING orders o
WHERE u.id = o.user_id 
  AND o.status = 'cancelled';

-- 清空表（比 DELETE 快很多）
TRUNCATE TABLE users;

35.5 COPY：批量导入的王者

好了，重点来了。COPY 命令是 PG 批量导入数据的利器。我做过测试，同样 100 万行数据，用 INSERT 逐行插入要 5 分钟，用 COPY 只要 10 秒。差距就是这么大。

-- 从文件导入 CSV
COPY users (name, age, email) 
FROM '/tmp/users.csv' 
DELIMITER ',' 
CSV HEADER;

-- 导出到文件
COPY users TO '/tmp/users_backup.csv' 
DELIMITER ',' 
CSV HEADER;

-- 从标准输入导入（程序里常用）
COPY users (name, age, email) 
FROM STDIN 
DELIMITER ',' 
CSV;

说白了，COPY 走的是底层协议，绕过了 SQL 解析层，所以快得离谱。

35.6 知识体系总览

下面这张图，是我梳理的 PostgreSQL 数据操作核心逻辑。你看一眼就能明白各个命令之间的关系。

35.7 实战：用 COPY 批量导入百万数据

最后，我分享一个实际项目中的例子。当时需要把 200 万条历史订单数据从 CSV 导入 PG。

-- 1. 创建临时表（先导入，再清洗）
CREATE TEMP TABLE orders_temp (
    order_id TEXT,
    user_id INTEGER,
    amount NUMERIC(10,2),
    status TEXT,
    created_at TEXT
);

-- 2. 用 COPY 批量导入（10秒搞定）
COPY orders_temp (order_id, user_id, amount, status, created_at)
FROM '/data/orders_2023.csv'
DELIMITER ','
CSV HEADER;

-- 3. 数据清洗后插入正式表
INSERT INTO orders (order_id, user_id, amount, status, created_at)
SELECT 
    order_id::BIGINT,
    user_id,
    amount,
    CASE WHEN status = '1' THEN 'paid'
         WHEN status = '2' THEN 'shipped'
         ELSE 'cancelled'
    END,
    created_at::TIMESTAMP
FROM orders_temp
WHERE amount > 0;

好了，这一章的内容就到这里。INSERT、SELECT、UPDATE、DELETE 是基本功，COPY 是性能利器。你把这些练熟了，PG 的数据操作基本就没什么能难住你的了。

---

公众号:蓝海资料掘金营，微信deep3321