PostgreSQL: PostgreSQL数据增删改操作

最后更新：2026-08-26

数据的增删改（DML）是日常开发最频繁的操作——PostgreSQL 在标准 SQL 基础上增加了 RETURNING 和 UPSERT 两大特色功能。

1. 你将学到

INSERT INTO 单行/多行/查询插入
RETURNING 子句（PG 特色：返回受影响的行）
UPDATE / DELETE 与 RETURNING
UPSERT：INSERT ON CONFLICT（PG 特色）
TRUNCATE TABLE 清空表
事务中的 DML（BEGIN / COMMIT / ROLLBACK）

2. 一个运营人员的真实故事

(1) 痛点：批量导入商品遇到重复数据

Charlie 需要批量导入 5,000 条商品数据到电商数据库，但部分商品已经存在（按 name 判断）。他需要：

已存在的商品：更新价格和库存（不报错）
新商品：正常插入
导入后：立刻知道哪些是新插入的、哪些是更新的

(2) UPSERT + RETURNING 的解法

PostgreSQL 的 INSERT ON CONFLICT（UPSERT）一条 SQL 同时处理插入和更新，RETURNING 子句返回受影响行：

SQL

-- Upsert: insert new products, update existing ones
INSERT INTO products (name, price, stock, category)
VALUES ('Running Shoes', 99.99, 200, 'Footwear')
ON CONFLICT (name)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock
RETURNING id, name, price, stock,
    CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation;

(3) 收益

无需先 SELECT 再决定 INSERT 或 UPDATE（减少一次查询）
无并发竞态条件（原子操作）
RETURNING 立刻返回结果，无需第二次查询
批量导入效率提升 10 倍以上

3. INSERT

(1) 基本语法

SQL

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
RETURNING * | column_list;

▶ 示例：单行插入

SQL

-- Insert a single row
INSERT INTO users (email, name, password_hash)
VALUES ('charlie@example.com', 'Charlie', '$2a$12$hash123');

-- Insert with RETURNING (get the auto-generated id)
INSERT INTO users (email, name, password_hash)
VALUES ('diana@example.com', 'Diana', '$2a$12$hash456')
RETURNING id, email, created_at;

TEXT 📖 仅展示

 id |       email          |          created_at
----+----------------------+-------------------------------
  5 | diana@example.com    | 2026-07-13 10:30:00+00

▶ 示例：多行插入

SQL

-- Insert multiple rows in one statement (faster than multiple INSERTs)
INSERT INTO products (name, price, stock, category) VALUES
    ('Wireless Mouse', 29.99, 500, 'Accessories'),
    ('USB-C Cable', 12.99, 1000, 'Accessories'),
    ('Mechanical Keyboard', 79.99, 200, 'Input Devices'),
    ('Monitor Stand', 49.99, 150, 'Accessories'),
    ('Webcam HD', 59.99, 300, 'Video');

TEXT 📖 仅展示

INSERT 0 1

▶ 示例：从查询插入

SQL

-- Create an archive table and copy data from the original
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);

-- Insert rows from a SELECT query
INSERT INTO orders_archive
SELECT * FROM orders
WHERE status = 'cancelled'
  AND created_at < NOW() - INTERVAL '90 days';

TEXT 📖 仅展示

INSERT 0 1

4. RETURNING 子句（PG 特色）

RETURNING 是 PostgreSQL 的独有功能——INSERT / UPDATE / DELETE 后直接返回受影响的行，无需额外 SELECT。

(1) 各操作中的 RETURNING

操作	语法	返回内容
INSERT	`INSERT ... RETURNING *`	新插入的行
UPDATE	`UPDATE ... RETURNING *`	更新后的行
DELETE	`DELETE ... RETURNING *`	被删除的行

对比	MySQL	PostgreSQL
获取 INSERT 后的 ID	`SELECT LAST_INSERT_ID()`	`INSERT ... RETURNING id`
获取 DELETE 的行	先 SELECT 再 DELETE	`DELETE ... RETURNING *`
获取 UPDATE 后的值	先 UPDATE 再 SELECT	`UPDATE ... RETURNING *`

▶ 示例：RETURNING 的妙用

SQL

-- INSERT: get auto-generated values immediately
INSERT INTO users (email, name, password_hash)
VALUES ('eve@example.com', 'Eve', '$2a$12$hash789')
RETURNING id, email, created_at;

-- UPDATE: see before and after values
UPDATE products
SET price = 69.99, stock = stock - 10
WHERE id = 1
RETURNING id, name,
    price AS new_price,
    stock AS new_stock;

-- DELETE: archive before deleting
WITH deleted AS (
    DELETE FROM orders
    WHERE status = 'cancelled'
      AND created_at < NOW() - INTERVAL '1 year'
    RETURNING *
)
INSERT INTO orders_archive SELECT * FROM deleted;

TEXT 📖 仅展示

INSERT 0 1

5. UPDATE

(1) 基本语法

SQL

UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition]
[RETURNING * | column_list];

▶ 示例：基本更新

SQL

-- Update a single row
UPDATE users
SET name = 'Alice Smith', updated_at = NOW()
WHERE email = 'alice@example.com';

-- Update with condition
UPDATE products
SET price = price * 0.9  -- 10% discount
WHERE category = 'Accessories' AND stock > 100;

-- Update with RETURNING
UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock > 0
RETURNING id, name, stock;

TEXT 📖 仅展示

-- SQL 语句执行成功

▶ 示例：基于 JOIN 的更新

SQL

-- Update order total based on order items
UPDATE orders o
SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items oi
    WHERE oi.order_id = o.id
)
WHERE o.status = 'pending';

-- Update using FROM clause (PG extension)
UPDATE orders o
SET total_amount = oi_sum.total
FROM (
    SELECT order_id, SUM(quantity * unit_price) AS total
    FROM order_items
    GROUP BY order_id
) oi_sum
WHERE o.id = oi_sum.order_id
  AND o.status = 'pending';

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

⚠️ 注意： UPDATE 没有 WHERE 子句会更新整张表！这是最常见的危险操作。始终先写 WHERE 再写 SET，养成习惯。

6. DELETE

(1) 基本语法

SQL

DELETE FROM table_name
[WHERE condition]
[RETURNING * | column_list];

▶ 示例：删除操作

SQL

-- Delete specific rows
DELETE FROM products
WHERE stock = 0 AND is_available = false
RETURNING id, name;

-- Delete with subquery
DELETE FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE is_active = false
);

-- Delete all rows (use TRUNCATE instead for large tables!)
-- DELETE FROM logs;  -- slow, generates WAL for each row

TEXT 📖 仅展示

DELETE 2

(2) DELETE vs TRUNCATE

维度	DELETE	TRUNCATE
速度	逐行删除，慢	一次性清空，极快
事务	可回滚（在事务中）	也可回滚（在事务中）
触发器	触发行级触发器	不触发行级触发器
序列重置	不重置	可重置（RESTART IDENTITY）
WHERE	支持	不支持（清空全表）
适用	删除部分行	清空全表

▶ 示例：TRUNCATE 清空表

SQL

-- Truncate one table (fast, resets storage)
TRUNCATE TABLE logs;

-- Truncate multiple tables at once
TRUNCATE TABLE order_items, orders;

-- Cascade: also truncate tables with foreign key references
TRUNCATE TABLE users CASCADE;

-- Reset auto-increment sequences
TRUNCATE TABLE products RESTART IDENTITY;

TEXT 📖 仅展示

-- SQL 语句执行成功

7. UPSERT（INSERT ON CONFLICT）

UPSERT 是 PostgreSQL 最实用的特色功能之一——当插入数据与已有行冲突时，自动执行更新而非报错。

(1) UPSERT 流程图

graph TB
    START[INSERT row] --> CONFLICT{Unique constraint<br/>conflict?}
    CONFLICT -->|No conflict| INSERT[Insert new row<br/>RETURN inserted]
    CONFLICT -->|Conflict!| ACTION{ON CONFLICT<br/>action?}
    ACTION -->|DO NOTHING| SKIP[Skip this row<br/>No error, no update]
    ACTION -->|DO UPDATE| UPDATE[Update existing row<br/>using EXCLUDED]
    UPDATE --> RET2[RETURN updated row]

(2) 语法

SQL

INSERT INTO table_name (column_list)
VALUES (value_list)
ON CONFLICT (column_name | constraint_name)
DO NOTHING | DO UPDATE SET column = EXCLUDED.column ...
[RETURNING *];

关键字	说明
`ON CONFLICT (column)`	指定冲突检测的列（必须有 UNIQUE 约束或主键）
`ON CONFLICT ON CONSTRAINT name`	指定具体的约束名
`DO NOTHING`	冲突时跳过（不报错，不更新）
`DO UPDATE SET ...`	冲突时执行更新
`EXCLUDED`	虚拟表，包含原本要插入的值

▶ 示例：DO NOTHING（忽略重复）

SQL

-- Insert only if email doesn't already exist
INSERT INTO users (email, name, password_hash)
VALUES ('alice@example.com', 'Alice', '$2a$12$newhash')
ON CONFLICT (email) DO NOTHING;
-- No error if email already exists

TEXT 📖 仅展示

INSERT 0 1

▶ 示例：DO UPDATE（更新已有行）

SQL

-- Upsert: insert new products, update price/stock for existing ones
INSERT INTO products (name, price, stock, category)
VALUES
    ('Running Shoes', 99.99, 200, 'Footwear'),
    ('USB-C Cable', 14.99, 800, 'Accessories'),
    ('New Product', 39.99, 50, 'Gadgets')
ON CONFLICT (name)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock,
    category = EXCLUDED.category
RETURNING id, name, price, stock;

TEXT 📖 仅展示

INSERT 0 1

▶ 示例：条件 UPSERT（仅特定情况更新）

SQL

-- Only update if the new price is lower
INSERT INTO products (name, price, stock, category)
VALUES ('Running Shoes', 79.99, 100, 'Footwear')
ON CONFLICT (name)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = EXCLUDED.stock
WHERE EXCLUDED.price < products.price;
-- Only updates if new price is cheaper than current price

TEXT 📖 仅展示

INSERT 0 1

8. 事务中的 DML

(1) 事务基本操作

命令	说明
`BEGIN` 或 `START TRANSACTION`	开始事务
`COMMIT`	提交事务（永久保存）
`ROLLBACK`	回滚事务（撤销所有修改）
`SAVEPOINT name`	创建保存点
`ROLLBACK TO SAVEPOINT name`	回滚到保存点

▶ 示例：事务保护批量操作

SQL

-- Begin a transaction for batch import
BEGIN;

-- Insert order and items as a unit
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 0, 'pending')
RETURNING id;

-- Assume the above returned id = 10
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
    (10, 1, 2, 89.99),
    (10, 2, 5, 12.99);

-- Update order total
UPDATE orders
SET total_amount = (2 * 89.99 + 5 * 12.99)
WHERE id = 10;

-- Verify before committing
SELECT * FROM orders WHERE id = 10;
SELECT * FROM order_items WHERE order_id = 10;

-- All good? Commit
COMMIT;

-- Something wrong? Rollback everything
-- ROLLBACK;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

9. 完整示例：商品批量导入

SQL

-- ============================================
-- Complete example: bulk product import with UPSERT
-- Charlie imports 5000 products, some already exist
-- ============================================

-- Step 1: Create a temporary import table
CREATE TEMP TABLE import_products (
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INTEGER DEFAULT 0,
    category VARCHAR(50)
);

-- Step 2: Load import data (simulated with sample rows)
INSERT INTO import_products (name, price, stock, category) VALUES
    ('Running Shoes', 99.99, 200, 'Footwear'),      -- already exists
    ('Laptop Backpack', 59.99, 300, 'Accessories'),   -- already exists, price changed
    ('Smart Water Bottle', 34.99, 500, 'Gadgets'),    -- new product
    ('Wireless Earbuds', 79.99, 400, 'Audio'),        -- new product
    ('Yoga Mat', 24.99, 600, 'Fitness');              -- new product

-- Step 3: Upsert all import data in one statement
BEGIN;

INSERT INTO products (name, price, stock, category)
SELECT name, price, stock, category
FROM import_products
ON CONFLICT (name)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock
RETURNING id, name, price, stock;

-- Step 4: Verify results
SELECT id, name, price, stock
FROM products
WHERE name IN ('Running Shoes', 'Laptop Backpack',
               'Smart Water Bottle', 'Wireless Earbuds', 'Yoga Mat')
ORDER BY name;

-- Step 5: Clean up
DROP TABLE import_products;

COMMIT;

❓ 常见问题

Q INSERT ON CONFLICT 和 REPLACE INTO（MySQL）有什么区别？

A MySQL 的 REPLACE INTO 实际上是 DELETE + INSERT，会删除旧行再插入新行，导致自增 ID 变化、触发器触发、外键断裂。PG 的 ON CONFLICT DO UPDATE 是真正的原地更新，ID 不变，不触发 DELETE 触发器。

Q EXCLUDED 是什么？

A EXCLUDED 是 PG 在 UPSERT 中提供的虚拟表，包含原本要 INSERT 但因冲突未能插入的值。用它来引用新值，与表中已有的旧值区分。例如 EXCLUDED.price 是新价格，products.price 是旧价格。

Q 批量插入多少行最合适？

A 单条 INSERT 多值建议 100-1000 行。超过 1000 行可能触发 SQL 解析性能问题。更大的批量建议用 COPY 命令（比 INSERT 快 5-10 倍，后续备份课讲解）。

Q UPDATE 忘记写 WHERE 会怎样？

A 会更新整张表！这是 SQL 最危险的错误之一。防护措施：1）始终先写 WHERE 再写 SET；2）在事务中操作，先 SELECT 确认范围再 UPDATE；3）设置 sql_safe_updates = on（psql 中执行）。

Q RETURNING 能否用于 CTE 中？

A 可以！这是 PG 非常强大的模式——DELETE ... RETURNING 配合 INSERT ... SELECT 实现数据迁移：WITH moved AS (DELETE FROM active WHERE ... RETURNING *) INSERT INTO archive SELECT * FROM moved。

Q TRUNCATE 可以回滚吗？

A 可以！在事务中 TRUNCATE 后 ROLLBACK 即可恢复。这与 MySQL 不同（MySQL 的 TRUNCATE 是 DDL，无法回滚）。PG 的 TRUNCATE 在事务中是安全的。

📖 小节

INSERT 支持单行、多行、查询插入，多行插入效率远高于逐条插入
RETURNING 子句（PG 特色）让 INSERT/UPDATE/DELETE 直接返回受影响行，无需二次查询
UPDATE 支持基于 JOIN 的更新（FROM 子句），比子查询更清晰
DELETE 逐行删除（慢），TRUNCATE 一次性清空（快），TRUNCATE 可在事务中回滚
UPSERT（INSERT ON CONFLICT）是 PG 核心特色：一条 SQL 处理"不存在则插入，已存在则更新"
EXCLUDED 虚拟表引用新插入值，与表中原值区分
事务（BEGIN/COMMIT/ROLLBACK）保护批量操作，出错可回滚

📝 作业

基础题（难度⭐）：创建一个 tags 表（id SERIAL 主键、name VARCHAR(50) UNIQUE），插入 5 个标签，用 RETURNING 返回每个插入行的 id 和 name。
进阶题（难度⭐⭐）：在本课 products 表上执行 UPSERT——插入 3 条商品数据，其中 1 条 name 已存在。已存在的商品更新 price，新商品正常插入。用 RETURNING 显示操作结果。
挑战题（难度⭐⭐⭐）：在一个事务中完成以下操作：1）创建 products_backup 表（结构与 products 相同）；2）将 products 中 price < 30 的商品用 DELETE ... RETURNING + INSERT 迁移到 products_backup；3）验证迁移结果后 COMMIT。写出完整的 SQL 脚本。

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

PostgreSQL: PostgreSQL数据增删改操作

1. 你将学到

2. 一个运营人员的真实故事

(1) 痛点：批量导入商品遇到重复数据

(2) UPSERT + RETURNING 的解法

(3) 收益

3. INSERT

(1) 基本语法

▶ 示例：单行插入

▶ 示例：多行插入

▶ 示例：从查询插入

4. RETURNING 子句（PG 特色）

(1) 各操作中的 RETURNING

▶ 示例：RETURNING 的妙用

5. UPDATE

(1) 基本语法

▶ 示例：基本更新

▶ 示例：基于 JOIN 的更新

6. DELETE

(1) 基本语法

▶ 示例：删除操作

(2) DELETE vs TRUNCATE

▶ 示例：TRUNCATE 清空表

7. UPSERT（INSERT ON CONFLICT）

(1) UPSERT 流程图

(2) 语法

▶ 示例：DO NOTHING（忽略重复）

▶ 示例：DO UPDATE（更新已有行）

▶ 示例：条件 UPSERT（仅特定情况更新）

8. 事务中的 DML

(1) 事务基本操作

▶ 示例：事务保护批量操作

9. 完整示例：商品批量导入

❓ 常见问题

📖 小节

📝 作业

🙏 帮我们做得更好