Home

深色模式

PostgreSQL 常用查询与修改 ​

概述 ​

装好了数据库、建好了表，接下来就是最日常的 SQL：查、插、改、删。真正高频用到的语法没有想象中那么多，但有几个 PostgreSQL 里特别顺手的点，最好早点养成习惯，比如 RETURNING、ON CONFLICT 和显式事务。

这一篇不讲偏分析型的复杂 SQL，只围绕后台业务里最常见的动作展开。示例主要沿用前一篇里的 users 和 orders 两张表。

基础查询 ​

查询某个用户：

sql
SELECT id, email, nickname, created_at
FROM users
WHERE id = 1;

按状态和创建时间查询订单列表：

sql
SELECT id, order_no, amount, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

这里有两个实用习惯：

  • 明确写出需要的列，不要默认 SELECT *
  • LIMIT 通常和 ORDER BY 一起用，别让分页结果随缘飘动

插入返回 ​

PostgreSQL 的 RETURNING 很实用，插完可以直接把新记录拿回来：

sql
INSERT INTO users (email, nickname)
VALUES ('tom@example.com', 'Tom')
RETURNING id, email, nickname, created_at;

插订单也一样：

sql
INSERT INTO orders (order_no, user_id, status, amount)
VALUES ('ORD-20260421-0001', 1, 'pending', 199.00)
RETURNING id, order_no, status;

这样应用层就不用再补一条查询去拿新数据。

更新删除 ​

更新订单状态：

sql
UPDATE orders
SET status = 'paid', paid_at = NOW(), updated_at = NOW()
WHERE id = 10
RETURNING id, status, paid_at;

删除某个测试账号：

sql
DELETE FROM users
WHERE id = 99
RETURNING id, email;

把 RETURNING 带上有两个好处：

  • 能直接确认到底改到了哪条数据
  • 脚本执行时更容易做日志记录

真正危险的不是 UPDATE 和 DELETE 本身，而是忘了 WHERE。这个问题不会因为会写 SQL 就自动消失。

关联查询 ​

查订单时连带查用户信息：

sql
SELECT
    o.id,
    o.order_no,
    o.amount,
    o.status,
    u.email,
    u.nickname
FROM orders AS o
JOIN users AS u ON u.id = o.user_id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;

这里的写法很朴素，但已经是后台列表页最常见的样子了。给表起个短别名，查询会清爽很多。

聚合查询 ​

统计每个状态的订单数和总金额：

sql
SELECT
    status,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY status
ORDER BY status;

做报表时，聚合和明细查询通常会分开写。别指望一条“全都要”的 SQL 同时把分页列表和完整统计都处理漂亮。

冲突处理 ​

例如邮箱唯一，重复写入时更新昵称：

sql
INSERT INTO users (email, nickname)
VALUES ('tom@example.com', 'Tom')
ON CONFLICT (email)
DO UPDATE SET
    nickname = EXCLUDED.nickname,
    updated_at = NOW()
RETURNING id, email, nickname;

这里的 EXCLUDED 表示这次准备插入但发生冲突的那条新数据。

ON CONFLICT 很适合：

  • 幂等写入
  • 第三方回调重复投递
  • 根据唯一键做 upsert

但前提是冲突目标本身要有唯一约束或唯一索引，不然数据库也不知道该按什么判断冲突。

事务控制 ​

如果一个操作要同时改多张表，或者中间任何一步失败都应该整体回滚，就应该用事务。

例如支付成功后，同时更新订单状态和记一条支付日志：

sql
BEGIN;

UPDATE orders
SET status = 'paid', paid_at = NOW(), updated_at = NOW()
WHERE id = 10;

INSERT INTO payment_logs (order_id, action, created_at)
VALUES (10, 'paid', NOW());

COMMIT;

如果中间发现条件不满足，可以回滚：

sql
ROLLBACK;

很多业务 bug 不是 SQL 不会写，而是本该放在一个事务里的多步操作被拆开执行了。

行级锁定 ​

有些场景不只是“多步一起成功”，还要求同一时间只能有一个事务改某条记录，例如扣库存、抢单、处理支付回调。这个时候常见写法是先锁住目标行：

sql
BEGIN;

SELECT id, status
FROM orders
WHERE id = 10
FOR UPDATE;

UPDATE orders
SET status = 'paid', updated_at = NOW()
WHERE id = 10;

COMMIT;

FOR UPDATE 不是所有事务都要用，但碰到“同一行会被并发修改”的业务时，它通常比在应用层自己猜时序更稳。

使用习惯 ​

日常写 SQL 时，比较稳的顺序通常是：

  1. 先 SELECT 确认目标数据
  2. 再写 UPDATE 或 DELETE
  3. 大一点的修改放进事务
  4. 需要拿结果时直接用 RETURNING
  5. 遇到幂等写入优先考虑 ON CONFLICT

这套动作不花哨，但基本覆盖了大多数后台系统每天都在做的事情。

常见失误 ​

分页只写 LIMIT，不写 ORDER BY ​

这样同一页结果并不稳定，数据一变化，分页顺序就可能飘。

UPDATE、DELETE 直接上手写 ​

先写 SELECT 确认范围，再改数据，能少很多手滑事故。

想做幂等更新，却没有唯一约束 ​

ON CONFLICT 不是魔法，它依赖数据库里已经存在明确的唯一边界。

参考 ​