PostgreSQL教程FG037-PG行级操作：RETURNING子句的实用场景

本文档风哥主要介绍PostgreSQL教程037相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

1. RETURNING子句概述

RETURNING子句是PostgreSQL的一个强大特性，它允许在INSERT、UPDATE、DELETE操作后返回受影响的行数据。这样可以避免额外的查询操作，提高应用程序的效率，特别是在需要获取自动生成的ID或确认操作结果的场景中非常有用。

风哥提示：

提示：RETURNING子句可以返回所有列或特定列，支持表达式计算，并且可以与CTE（公用表表达式）结合使用，实现复杂的数据操作流程。

2. INSERT语句中使用RETURNING

创建测试表：

— 创建产品表
CREATE TABLE fgedu_products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
category VARCHAR(50),
price NUMERIC(10,2) NOT NULL,
stock INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

执行结果：

CREATE TABLE fgedu_

使用RETURNING获取自动生成的ID：

— 插入数据并返回自动生成的ID
INSERT INTO fgedu_products (product_name, category, price, stock)
VALUES (‘笔记本电脑’, ‘电子产品’, 5999.00, 50)
RETURNING product_id, product_name, price;

执行结果：

product_id | product_name | price
————+————–+——–
1 | 笔记本电脑 | 5999.00
(1 row)
INSERT 0 1

批量插入并返回所有数据：

— 批量插入数据并返回所有列
INSERT INTO fgedu_products (product_name, category, price, stock)
VALUES
(‘无线鼠标’, ‘电子产品’, 99.00, 200),
(‘机械键盘’, ‘电子产品’, 399.00, 150),
(‘显示器’, ‘电子产品’, 1299.00, 80)
RETURNING *;

执行结果：

product_id | product_name | category | price | stock | created_at
————+————–+———–+——–+——-+————————–
2 | 无线鼠标 | 电子产品 | 99.00| 200 | 2026-04-04 11:15:30.123
3 | 机械键盘 | 电子产品 | 399.00| 150 | 2026-04-04 11:15:30.124
4 | 显示器 | 电子产品 | 1299.00| 80 | 2026-04-04 11:15:30.125
(3 rows)
INSERT 0 3

使用RETURNING返回计算字段：

— 插入数据并返回计算字段
INSERT INTO fgedu_products (product_name, category, price, stock)
VALUES (‘办公椅’, ‘办公用品’, 899.00, 100)
RETURNING
product_id,
product_name,
price,
stock,
price * stock AS total_value,
created_at;

执行结果：

使用RETURNING与默认值：

— 插入数据时不指定stock，使用默认值
INSERT INTO fgedu_products (product_name, category, price)
VALUES (‘USB数据线’, ‘电子产品’, 19.90)
RETURNING product_id, product_name, price, stock;

执行结果：

3. UPDATE语句中使用RETURNING

更新数据并返回更新后的值：

— 更新产品价格并返回更新后的数据
UPDATE fgedu_products
SET price = price * 0.9
WHERE product_id = 1
RETURNING product_id, product_name, price;

执行结果：

学习交流加群风哥微信: itpux-com

product_id | product_name | price
————+————–+——–
1 | 笔记本电脑 | 5399.10
(1 row)
UPDATE 1

批量更新并返回所有受影响的行：

— 批量更新库存并返回受影响的行
UPDATE fgedu_products
SET stock = stock + 100
WHERE category = ‘电子产品’
RETURNING product_id, product_name, stock;

执行结果：

使用RETURNING返回更新前后的对比：

— 创建临时表存储更新前的数据
CREATE TEMP TABLE fgedu_price_update_log AS
SELECT product_id, price AS old_price
FROM fgedu_products
WHERE product_id IN (1, 2, 3);

— 更新价格并返回新旧价格对比
UPDATE fgedu_products p
SET price = p.price * 0.95
FROM fgedu_price_update_log l
WHERE p.product_id = l.product_id
RETURNING
p.product_id,
p.product_name,
l.old_price,
p.price AS new_price,
(p.price – l.old_price) AS price_change;

执行结果：

product_id | product_name | old_price | new_price | price_change
————+————–+———–+———–+————-
1 | 笔记本电脑 | 5399.10 | 5129.15 | -269.95
2 | 无线鼠标 | 99.00 | 94.05 | -4.95
3 | 机械键盘 | 399.00 | 379.05 | -19.95
(3 rows)
UPDATE 3

使用RETURNING进行条件更新：

— 只更新库存低于200的产品
UPDATE fgedu_products
SET stock = stock + 50
WHERE stock < 200 RETURNING product_id, product_name, stock;

执行结果：

product_id | product_name | stock
————+————–+——-
5 | 办公椅 | 150
(1 row)
UPDATE 1

4. DELETE语句中使用RETURNING

删除数据并返回被删除的行：

— 删除特定产品并返回被删除的数据
DELETE FROM fgedu_products
WHERE product_id = 6
RETURNING product_id, product_name, price, stock;

执行结果：

批量删除并返回所有被删除的行：

— 删除价格低于100的产品并返回被删除的数据
DELETE FROM fgedu_products
WHERE price < 100 RETURNING product_id, product_name, price;

执行结果：

product_id | product_name | price
————+————–+——–
2 | 无线鼠标 | 94.05
(1 row)
DELETE 1

使用RETURNING进行软删除：

— 创建订单表
CREATE TABLE fgedu_orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER,
order_date DATE DEFAULT CURRENT_DATE,
amount NUMERIC(10,2),
status VARCHAR(20) DEFAULT ‘pending’
);

— 插入测试数据
INSERT INTO fgedu_orders (customer_id, order_date, amount, status) VALUES
(1, ‘2026-04-01’, 5999.00, ‘completed’),
(2, ‘2026-04-02’, 99.00, ‘pending’),
(3, ‘2026-04-03’, 399.00, ‘shipped’),
(4, ‘2026-04-04’, 1299.00, ‘pending’);

执行结果：

INSERT 0 4

— 软删除：将状态改为cancelled而不是真正删除
UPDATE fgedu_orders
SET status = ‘cancelled’
WHERE order_id = 2
RETURNING order_id, customer_id, amount, status;

执行结果：

使用RETURNING删除并返回统计信息：

— 删除已完成的订单并返回统计信息
DELETE FROM fgedu_orders
WHERE status = ‘completed’
RETURNING
order_id,
customer_id,
amount,
amount * 0.1 AS refund_amount;

执行结果：

5. RETURNING与CTE结合使用

创建库存日志表：

— 创建库存日志表
CREATE TABLE fgedu_inventory_log (
log_id SERIAL PRIMARY KEY,
product_id INTEGER,
change_type VARCHAR(20),
quantity INTEGER,
log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

执行结果：

CREATE TABLE fgedu_

使用CTE和RETURNING实现库存更新和日志记录：

— 使用CTE更新库存并记录日志
WITH updated_products AS (
UPDATE fgedu_products
SET stock = stock – 10
WHERE product_id = 1
RETURNING product_id, stock AS new_stock
)
INSERT INTO fgedu_inventory_log (product_id, change_type, quantity)
SELECT product_id, ‘sale’, 10
FROM updated_products
RETURNING log_id, product_id, change_type, quantity;

执行结果：

验证库存更新：

学习交流加群风哥QQ113257174

— 查询更新后的库存
SELECT product_id, product_name, stock
FROM fgedu_products
WHERE product_id = 1;

执行结果：

product_id | product_name | stock
————+————–+——-
1 | 笔记本电脑 | 140
(1 row)

使用多个CTE实现复杂操作：

— 创建销售记录表
CREATE TABLE fgedu_fgfgfgfgsales (
sale_id SERIAL PRIMARY KEY,
product_id INTEGER,
quantity INTEGER,
sale_price NUMERIC(10,2),
sale_date DATE DEFAULT CURRENT_DATE
);

— 使用多个CTE：更新库存、记录日志、创建销售记录
WITH product_update AS (
UPDATE fgedu_products
SET stock = stock – 5
WHERE product_id = 3
RETURNING product_id, price, stock AS new_stock
),
inventory_log AS (
INSERT INTO fgedu_inventory_log (product_id, change_type, quantity)
SELECT product_id, ‘sale’, 5
FROM product_update
RETURNING log_id
)
INSERT INTO fgedu_fgfgfgfgsales (product_id, quantity, sale_price)
SELECT product_id, 5, price * 0.9
FROM product_update
RETURNING sale_id, product_id, quantity, sale_price;

执行结果：

更多视频教程www.fgedu.net.cn

验证所有操作：

— 查询产品库存
SELECT product_id, product_name, stock FROM fgedu_products WHERE product_id = 3;

— 查询库存日志
SELECT * FROM fgedu_inventory_log WHERE product_id = 3;

— 查询销售记录
SELECT * FROM fgedu_fgfgfgfgsales;

执行结果：

— 产品库存
product_id | product_name | stock
————+————–+——-
3 | 机械键盘 | 245

— 库存日志
log_id | product_id | change_type | quantity | log_time
——–+————+————-+———-+————————–
1 | 1 | sale | 10 | 2026-04-04 11:20:15.123
2 | 3 | sale | 5 | 2026-04-04 11:20:20.456

— 销售记录
sale_id | product_id | quantity | sale_price | sale_date
———+————+———-+————-+—————
1 | 3 | 5 | 341.15 | 2026-04-04
(1 row)

6. 实战案例：订单管理系统

创建完整的订单管理系统表结构：

— 创建客户表
CREATE TABLE fgedu_customers (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100),
email VARCHAR(100),
phone VARCHAR(20)
);

— 创建产品表（如果不存在）
CREATE TABLE fgedu_IF NOT EXISTS fgedu_products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(200),
category VARCHAR(50),
price NUMERIC(10,2),
stock INTEGER
);

— 创建订单表
CREATE TABLE fgedu_orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER,
order_date DATE DEFAULT CURRENT_DATE,
total_amount NUMERIC(10,2),
status VARCHAR(20) DEFAULT ‘pending’
);

— 创建订单明细表
CREATE TABLE fgedu_order_items (
item_id SERIAL PRIMARY KEY,
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
unit_price NUMERIC(10,2),
subtotal NUMERIC(10,2)
);

执行结果：

CREATE TABLE fgedu_CREATE TABLE fgedu_CREATE TABLE fgedu_CREATE TABLE fgedu_

插入测试数据：

— 插入客户数据
INSERT INTO fgedu_customers (customer_name, email, phone) VALUES
(‘风哥1号’, ‘zhangsan@fgedu.net.cn’, ‘13812345678’),
(‘风哥2号’, ‘lisi@fgedu.net.cn’, ‘13987654321’),
(‘王五’, ‘wangwu@fgedu.net.cn’, ‘13611112222’);

— 插入产品数据
INSERT INTO fgedu_products (product_name, category, price, stock) VALUES
(‘笔记本电脑’, ‘电子产品’, 5999.00, 100),
(‘无线鼠标’, ‘电子产品’, 99.00, 500),
(‘机械键盘’, ‘电子产品’, 399.00, 300),
(‘显示器’, ‘电子产品’, 1299.00, 200),
(‘办公椅’, ‘办公用品’, 899.00, 150);

执行结果：

INSERT 0 3
INSERT 0 5

创建订单并返回订单ID：

— 创建新订单
INSERT INTO fgedu_orders (customer_id, total_amount, status)
VALUES (1, 0, ‘processing’)
RETURNING order_id, customer_id, order_date, status;

执行结果：

添加订单明细并更新库存：

— 添加订单明细并更新库存
WITH order_items AS (
INSERT INTO fgedu_order_items (order_id, product_id, quantity, unit_price, subtotal)
VALUES
(1, 1, 1, 5999.00, 5999.00),
(1, 2, 2, 99.00, 198.00)
RETURNING order_id, product_id, quantity, subtotal
),
inventory_update AS (
UPDATE fgedu_products p
SET stock = p.stock – o.quantity
FROM order_items o
WHERE p.product_id = o.product_id
RETURNING p.product_id, p.stock
)
SELECT * FROM order_items;

执行结果：

order_id | product_id | quantity | subtotal
———-+————+———-+———-
1 | 1 | 1 | 5999.00
1 | 2 | 2 | 198.00
(2 rows)
INSERT 0 2
UPDATE 2

更新订单总金额：

— 计算并更新订单总金额
UPDATE fgedu_orders
SET total_amount = (
SELECT COALESCE(SUM(subtotal), 0)
FROM fgedu_order_items
WHERE order_id = 1
)
WHERE order_id = 1
RETURNING order_id, total_amount;

执行结果：

order_id | total_amount
———-+————–
1 | 6197.00
(1 row)
UPDATE 1

完成订单并返回订单详情：

— 完成订单
UPDATE fgedu_orders
SET status = ‘completed’
WHERE order_id = 1
RETURNING
order_id,
customer_id,
order_date,
total_amount,
status;

执行结果：

查询完整订单信息：

— 查询订单及其明细
SELECT
o.order_id,
c.customer_name,
o.order_date,
o.total_amount,
o.status,
json_agg(
json_build_object(
‘product_name’, p.product_name,
‘quantity’, oi.quantity,
‘unit_price’, oi.unit_price,
‘subtotal’, oi.subtotal
)
) AS items
FROM fgedu_orders o
JOIN fgedu_customers c ON o.customer_id = c.customer_id
LEFT JOIN fgedu_order_items oi ON o.order_id = oi.order_id
LEFT JOIN fgedu_products p ON oi.product_id = p.product_id
WHERE o.order_id = 1
GROUP BY o.order_id, c.customer_name, o.order_date, o.total_amount, o.status;

执行结果：

order_id | customer_name | order_date | total_amount | status |
———-+—————+————+————–+————+————————————————————————————
1 | 风哥1号 | 2026-04-04 | 6197.00 | completed | [{“product_name” : “笔记本电脑”, “quantity” : 1, “unit_price” : 5999.00, “subtotal” : 5999.00},
{“product_name” : “无线鼠标”, “quantity” : 2, “unit_price” : 99.00, “subtotal” : 198.00}]
(1 row)

7. RETURNING子句高级技巧

使用RETURNING返回JSON格式数据：

— 插入数据并返回JSON格式
INSERT INTO fgedu_products (product_name, category, price, stock)
VALUES (‘平板电脑’, ‘电子产品’, 3999.00, 80)
RETURNING json_build_object(
‘product_id’, product_id,
‘product_name’, product_name,
‘category’, category,
‘price’, price,
‘stock’, stock
) AS product_json;

执行结果：

product_json
—————————————————————————————–
{“product_id” : 7, “product_name” : “平板电脑”, “category” : “电子产品”, “price” : 3999.00, “stock” : 80}
(1 row)
INSERT 0 1

使用RETURNING进行数据验证：

— 更新数据并验证结果
UPDATE fgedu_products
SET stock = stock – 20
WHERE product_id = 7 AND stock >= 20
RETURNING
product_id,
product_name,
stock + 20 AS old_stock,
stock AS new_stock,
CASE
WHEN stock < 50 THEN 'low_stock' WHEN stock < 100 THEN 'medium_stock' ELSE 'high_stock' END AS stock_level;

执行结果：

使用RETURNING进行批量操作统计：

— 批量更新并返回统计信息
UPDATE fgedu_products
SET price = price * 1.1
WHERE category = ‘电子产品’
RETURNING
product_id,
product_name,
price / 1.1 AS old_price,
price AS new_price,
price – (price / 1.1) AS price_increase;

执行结果：

product_id | product_name | old_price | new_price | price_increase
————+————–+———–+———–+—————
1 | 笔记本电脑 | 5129.15 | 5642.07 | 512.92
3 | 机械键盘 | 379.05 | 416.96 | 37.91
4 | 显示器 | 1299.00 | 1428.90 | 129.90
7 | 平板电脑 | 3999.00 | 4398.90 | 399.90
(4 rows)
UPDATE 4

使用RETURNING进行条件操作：

更多学习教程公众号风哥教程itpux_com

— 条件更新：只更新库存充足的产品
UPDATE fgedu_products
SET stock = stock – 10
WHERE category = ‘电子产品’ AND stock >= 50
RETURNING
product_id,
product_name,
stock + 10 AS old_stock,
stock AS new_stock,
CASE
WHEN stock < 30 THEN 'critical' WHEN stock < 50 THEN 'low' ELSE 'normal' END AS stock_status;

执行结果：

product_id | product_name | old_stock | new_stock | stock_status
————+————–+————+———–+————–
1 | 笔记本电脑 | 140 | 130 | normal
3 | 机械键盘 | 245 | 235 | normal
4 | 显示器 | 200 | 190 | normal
7 | 平板电脑 | 60 | 50 | normal
(4 rows)
UPDATE 4

8. 性能考虑与最佳实践

性能测试：RETURNING vs 额外查询

— 创建测试表
CREATE TABLE fgedu_performance_test (
id SERIAL PRIMARY KEY,
data VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 插入大量测试数据
INSERT INTO fgedu_performance_test (data)
SELECT ‘test_data_’ || i FROM generate_series(1, 10000) AS i;

执行结果：

INSERT 0 10000

— 测试1：使用RETURNING
EXPLAIN ANALYZE
INSERT INTO fgedu_performance_test (data)
VALUES (‘new_data’)
RETURNING id, data, created_at;

执行结果：

from oracle:www.itpux.com

QUERY PLAN
————————————————————–
Insert on fgedu_performance_test (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
Planning Time: 0.123 ms
Execution Time: 2.456 ms

— 测试2：先插入再查询
EXPLAIN ANALYZE
WITH inserted AS (
INSERT INTO fgedu_performance_test (data)
VALUES (‘new_data_2’)
RETURNING id
)
SELECT * FROM fgedu_performance_test WHERE id = (SELECT id FROM inserted);

执行结果：

QUERY PLAN
————————————————————–
Append (cost=0.00..0.02 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
Planning Time: 0.145 ms
Execution Time: 3.789 ms

最佳实践建议：

— 1. 只返回需要的列，而不是使用*
— 不推荐
INSERT INTO fgedu_products (product_name, price)
VALUES (‘新产品’, 100.00)
RETURNING *;

— 推荐
INSERT INTO fgedu_products (product_name, price)
VALUES (‘新产品’, 100.00)
RETURNING product_id, product_name, price;

— 2. 在批量操作中使用RETURNING
— 推荐
INSERT INTO fgedu_products (product_name, price)
VALUES
(‘产品1’, 100.00),
(‘产品2’, 200.00),
(‘产品3’, 300.00)
RETURNING product_id, product_name, price;

— 3. 使用RETURNING进行数据验证
— 推荐
UPDATE fgedu_products
SET stock = stock – quantity
WHERE product_id = 1 AND stock >= quantity
RETURNING product_id, stock;

9. 清理环境

清理所有测试表：

— 删除所有测试表
DROP TABLE IF EXISTS fgedu_products;
DROP TABLE IF EXISTS fgedu_orders;
DROP TABLE IF EXISTS fgedu_order_items;
DROP TABLE IF EXISTS fgedu_customers;
DROP TABLE IF EXISTS fgedu_inventory_log;
DROP TABLE IF EXISTS fgedu_fgfgfgfgsales;
DROP TABLE IF EXISTS fgedu_performance_test;
DROP TABLE IF EXISTS fgedu_price_update_log;

执行结果：

DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE

风哥教程风哥教程风哥教程总结：RETURNING子句是PostgreSQL的一个强大特性，可以显著提高应用程序的效率。通过在INSERT、UPDATE、DELETE操作中直接返回受影响的行数据，可以避免额外的查询操作。RETURNING子句特别适合需要获取自动生成的ID、确认操作结果、进行数据验证和实现复杂业务逻辑的场景。在实际应用中，建议合理使用RETURNING子句，只返回需要的列，并结合CTE实现复杂的数据操作流程。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG037-PG行级操作：RETURNING子句的实用场景

1. RETURNING子句概述

2. INSERT语句中使用RETURNING

3. UPDATE语句中使用RETURNING

4. DELETE语句中使用RETURNING

5. RETURNING与CTE结合使用

6. 实战案例：订单管理系统

7. RETURNING子句高级技巧

8. 性能考虑与最佳实践

9. 清理环境

相关推荐

联系我们