先聊点实在的。PostgreSQL 这几年在圈里的存在感越来越强不管你是刚接触数据库的新人还是从 MySQL 转过来的老手第一关都是同一道门槛把最基本的增删改查CRUD写得干净、高效、不出错。很多人觉得 SQL 嘛不就是 INSERT、SELECT、UPDATE、DELETE 那几句能有多难但实际一上手就会发现PG 的语法细节、数据类型约束、事务行为跟别的数据库还真不完全一样。这篇东西就是我基于自己实际使用 PG 的经验把这四个操作从语法到实践完整捋一遍中间会穿插一些只有踩过坑才懂的点希望对刚开始用 PostgreSQL 的朋友有帮助。这篇文章适合谁想快速上手 PG 的开发者、从 MySQL 迁移过来的后端工程师、以及学校里刚学完数据库原理准备做课程设计的同学。我尽量用实际能跑通的例子说话你跟着敲一遍基本就能覆盖日常开发里 80% 的写库场景。1. 动手之前的准备环境、客户端与建库建表1.1 选对版本和客户端工具省掉一半的折腾先说版本。PostgreSQL 的版本策略跟 MySQL 不太一样官方每一年出一个大版本比如 15、16、17每个大版本后续会有小版本更新。我的建议很简单能装新版就装新版比如当前的稳定版本 17或者至少 16。老版本虽然稳但很多新的特性比如MERGE语句的完善、更快的排序算法、UUID类型的原生支持在老版本里用不上。安装方式我见过各种折腾的Windows 用户去官网下载安装包下一步下一步就行但有个坑是pg_hba.conf的默认认证方式安装完你会发现本地用psql连没问题一旦想用 Navicat 或 DBeaver 远程连就报password authentication failed。Linux 用户如果用apt或yum装装完默认只监听localhost想远程访问得手动改postgresql.conf里的listen_addresses。这些都是老生常谈但每次都能坑到一批人。客户端工具方面我个人的使用顺序是命令行psql排查问题最快 DBeaver免费跨平台支持 PG 很好 Navicat功能全但收费。如果你是新手别一上来就依赖图形化工具先把psql玩熟因为很多线上故障排查场景只有命令行可用。1.2 建库、建用户、建表一步到位的基础操作登录之后第一件事往往是建库。注意 PG 里没有CREATE DATABASE IF NOT EXISTS这种写法所以你要先查一下目标库存不存在或者直接用CREATE DATABASE让它报错也无所谓。-- 创建数据库 CREATE DATABASE myapp; -- 创建一个专门的应用用户并设置密码 CREATE USER app_user WITH PASSWORD StrongPass_2024; -- 把库的权限给这个用户 GRANT ALL PRIVILEGES ON DATABASE myapp TO app_user;建表是重头戏。这里我要特别强调一个 PG 特色主键推荐用GENERATED AS IDENTITY而不是传统 MySQL 那种AUTO_INCREMENT也不是老 PG 教程里的SERIAL。CREATE TABLE users ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL UNIQUE, age INT CHECK (age 0 AND age 150), created_at TIMESTAMPTZ NOT NULL DEFAULT now() );为什么推荐GENERATED ALWAYS AS IDENTITY因为它更符合 SQL 标准而且不允许你手动往这个列里插入值从源头避免了主键冲突。SERIAL虽然也行但它本质是INT 一个序列历史包袱比较重。TIMESTAMPTZ是带时区的时间类型强烈建议所有时间字段都用它别用TIMESTAMP否则跨时区部署的时候你会哭。2. 插入数据INSERT不止是简单的“加一行”2.1 基础语法与单行插入的细节插入语句的常规写法大家都会但有几个细节我想专门拿出来说。INSERT INTO users (username, email, age) VALUES (zhangsan, zhangsanexample.com, 25);注意三件事第一如果id是GENERATED ALWAYS AS IDENTITY你绝对不能在 INSERT 语句里包含id列否则会直接报错cannot insert a non-DEFAULT value into column id。这是 PG 对数据一致性的坚持别跟它较劲老老实实省略。第二created_at有默认值now()所以也不用管它。但如果你希望插入时自定义时间那就显式指定如果希望这个字段“只允许数据库写、不允许应用写”那反过来用GENERATED ALWAYS或触发器。第三字符串用单引号不能用双引号。PG 里双引号是标识符表名、列名用的字符串必须单引号。这是新手最容易踩的语法坑。2.2 高效批量插入一次多行与 RETURNING批量插入是日常高频操作。两种写法-- 方式一单语句多行 VALUES INSERT INTO users (username, email, age) VALUES (user1, user1example.com, 20), (user2, user2example.com, 30), (user3, user3example.com, 40);这是 PG 官方推荐的高效方式。扩展一下插入之后想立刻拿到生成的主键怎么办用RETURNING子句这是 PG 的一大特色功能MySQL 到 8.0 才有类似能力而且还没 PG 用得顺手。INSERT INTO users (username, email, age) VALUES (wangwu, wangwuexample.com, 28) RETURNING id, username, created_at;RETURNING会把你插入的那行数据的指定字段直接返回省掉了一次额外的SELECT查询。在需要“插入后立刻使用主键”的业务里比如创建用户后立即建订单这个特性非常方便。我在实际项目中就经常用它把生成的记录完整返回给前端。2.3 插入冲突的处理ON CONFLICT 的使用心得这是 PG 相对其他数据库的一个很核心的差异化特性。假设你的操作需要“不存在就插入存在就更新”这句 SQL 可以直接实现INSERT INTO users (username, email, age) VALUES (lisi, lisiexample.com, 22) ON CONFLICT (email) DO UPDATE SET username EXCLUDED.username, age EXCLUDED.age;解释一下ON CONFLICT (email)的意思是当email唯一约束冲突时执行DO UPDATE后面的更新逻辑。EXCLUDED是一个特殊引用代表“你试图插入的那一行数据”。所以上面这句等价于如果邮箱存在就把用户名和年龄更新成新提交过来的值。还有另一种需求是“冲突就啥也不干别报错”INSERT INTO users (username, email, age) VALUES (tianqi, tianqiexample.com, 20) ON CONFLICT (email) DO NOTHING;这个特性在数据导入、爬虫落库、消息队列消费等“幂等写入”场景里特别实用。要是没有ON CONFLICT你得先查一遍再决定插还是更多一次往返不说还有并发差点。3. 查询数据SELECTPG 里最需要花时间学的部分3.1 基础查询与 WHERE 条件从简单到复合查询是 SQL 里最常用也最能拉开发差距的部分。基础语法-- 查询全部列 SELECT * FROM users; -- 查询指定列并排序 SELECT username, email, created_at FROM users ORDER BY created_at DESC; -- 带条件的查询 SELECT * FROM users WHERE age 18 AND age 60;WHERE条件里要注意 PG 的布尔逻辑是TRUE/FALSE/NULL三值逻辑。比如你想查“不是某个邮箱”的用户如果email字段存在 NULLWHERE email aaaexample.com会把这个 NULL 的行过滤掉因为NULL aaa结果是NULL而NULL在 WHERE 里等价于FALSE。这在数据清洗时非常容易把人搞懵。正确写法是SELECT * FROM users WHERE email aaaexample.com OR email IS NULL;还有一点PG 对字符串比较是区分大小写的WHERE username Zhangsan查不到zhangsan。这是数据库的标准行为但由于 MySQL 默认不区分大小写从 MySQL 转过来的同学经常在这里被坑。真要不区分大小写可以用ILIKE或者LOWER(username) zhangsan。3.2 分页查询LIMIT 和 OFFSET 的正确姿势PG 的分页语法是SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 40;LIMIT 20表示最多返回 20 行OFFSET 40表示跳过前 40 行。这是最常见的“第 3 页每页 20 条”的写法。但这个写法有个隐藏问题OFFSET 越大查询越慢。因为数据库要把前 40 行全找出来再丢弃深分页到第 10000 页时前面的扫描开销没法避免。更高效的方式是“基于游标的分页”或者叫“键集分页”用上一页最后一条数据的某个唯一字段做条件SELECT * FROM users WHERE id 40 ORDER BY id LIMIT 20;这种写法能走主键索引直接跳过去页数再深性能也不会劣化。日常开发里中小数据量用OFFSET没问题但如果你做的是后台管理列表数据量可能上百万从一开始就建议用基于id的键集分页。3.3 聚合与去重GROUP BY、DISTINCT、HAVING统计场景离不开聚合。-- 去重 SELECT DISTINCT username FROM users; -- 分组统计年龄 SELECT COUNT(*) AS total, AVG(age) AS avg_age, MAX(age) AS max_age FROM users;如果要在分组后过滤注意用HAVING而不是WHERESELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) 10;WHERE是分组前过滤原始行HAVING是分组后过滤聚合结果。这个区分很多新手搞不清记住一句话就行WHERE过滤的是“行”HAVING过滤的是“组”。DISTINCT虽然是标准功能但在 PG 里有个细节SELECT DISTINCT * FROM users会对所有列去重如果表里有一个字段是不同的比如created_at那DISTINCT就形同虚设一行都去不掉。所以去重前想清楚你到底按哪些字段判重。3.4 JOIN 联表查询内联、左联的区别实战增删改查里真正体现业务复杂度的就是查询而查询里最常用的又是JOIN。拿一个简单的例子users表和orders表关联。CREATE TABLE orders ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id), amount NUMERIC(10, 2) NOT NULL );内连接INNER JOINSELECT u.username, o.id AS order_id, o.amount FROM users u INNER JOIN orders o ON u.id o.user_id;这个查询只返回“有订单的用户”。如果你还想看到没有订单的用户就得用左连接LEFT JOINSELECT u.username, o.id AS order_id, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id;左连接时没有订单的用户o.id和o.amount会是 NULL。在排查数据缺失问题时我经常用“左连接 WHERE 右表 IS NULL”来找出没有关联记录的数据这比用NOT IN安全得多SELECT u.* FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.id IS NULL;顺带说一句NOT IN在右表存在 NULL 时会出现“查不到任何数据”的诡异现象。比如WHERE id NOT IN (1, 2, NULL)结果整个查询返回空集因为id 3和不等于NULL比较结果是NULL。用LEFT JOIN ... WHERE ... IS NULL则完全没有这个坑。4. 更新数据UPDATE改之前先想清楚影响范围4.1 基础 UPDATE 与千万级数据更新性能注意UPDATE 的基础写法没什么可说的UPDATE users SET age 26 WHERE username zhangsan;但这里有两个核心忠告。第一UPDATE 之前先 SELECT 确认影响范围。没有WHERE就是全表更新这在生产环境里是事故级别的操作。我见过有人把UPDATE users SET age 26直接执行全表年龄变成 26还好是在测试库。第二批量更新要注意事务大小。PG 的 UPDATE 不是直接改原数据而是“标记旧版本 写入新版本”这就是 MVCC多版本并发控制。一次更新一百万行会在表里产生一百万个旧版本事务提交时虽然速度还行但表会迅速膨胀后续的垃圾回收VACUUM压力巨大。如果确实要批量更新大表建议分批执行每批比如一万行中间加个短暂停顿这样对锁和磁盘 IO 更友好。4.2 RETURNING 与 CASE 条件更新UPDATE 同样支持RETURNING这是 PG 实用价值很高的特性。你可以直接拿到更新后的数据免去二次查询UPDATE users SET age age 1 WHERE username zhangsan RETURNING id, username, age, created_at;另一个实用写法是CASE条件更新。比如一个用户表要根据不同等级设置不同状态CASE可以一次UPDATE完成多分支更新UPDATE users SET level CASE WHEN points 10000 THEN VIP WHEN points 5000 THEN Gold ELSE Normal END WHERE active TRUE;这种方式在批量数据修正场景里很常用。注意CASE从上往下匹配第一个满足的条件生效所以要注意分支顺序。4.3 UPDATE 语句里的坑脏写与锁定冲突PostgreSQL 的行锁机制是基于 MVCC 的。两个事务同时 UPDATE 同一行时后提交的一方会阻塞而不是直接失败。这个阻塞等到第一个事务提交或回滚才结束。如果第一个事务一直不提交第二个事务会一直等直到超时lock_timeout默认没有限制可能无限等。这是很多“SQL 卡住”问题的根源。解决办法是设置合理的锁超时SET lock_timeout 5s;或者写代码时注意事务尽快提交不要在事务里做耗时的外部 API 调用。我在项目里踩到过一个很经典的坑一个事务里先UPDATE了某行然后又去调一个第三方登录接口第三方接口响应很慢结果这一行被锁了两分钟其它请求全部挤在这个锁上雪崩。这种问题靠 SQL 优化还解决不了得改业务代码把事务缩短。4.4 UPDATE JOIN用关联表的数据更新目标表有时你要用另一张表的数据来更新当前表比如用orders表的总消费金额去更新users表的total_spent字段。PG 没有UPDATE ... JOINMySQL 的写法但有UPDATE ... FROMUPDATE users u SET total_spent t.total FROM ( SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id ) t WHERE u.id t.user_id;这个写法很 PG执行效率也高。注意在UPDATE ... FROM中如果t里同一个user_id有多条记录比如你的子查询分组没写对PG 会“随机”用其中一条来更新不会报错。所以用之前一定要确认子查询目标字段是唯一的。5. 删除数据DELETE比看起来更讲究的操作5.1 基础 DELETE 与 TRUNCATE 的分工-- 删除指定条件的数据 DELETE FROM users WHERE username tianqi; -- 删除所有数据 DELETE FROM users;注意DELETE FROM users和TRUNCATE TABLE users的区别。DELETE是逐行删除走 MVCC速度慢但可以加WHERE并且会触发外键检查TRUNCATE是一次性清空表速度快得多但不能有WHERE而且会重置IDENTITY的序列值。如果你要清空一张表并让主键重新从 1 开始计TRUNCATE TABLE users RESTART IDENTITY;是正解。TRUNCATE还有个隐含行为是它会获取表级ACCESS EXCLUSIVE锁清空时这张表对外完全不可用。所以别在生产高峰时间去TRUNCATE哪怕表里只有几条数据锁冲突也可能把整个业务打停。5.2 DELETE 的 RETURNING 与回收空间DELETE 同样支持RETURNING可以拿到被删除的行DELETE FROM users WHERE username zhangsan RETURNING id, username;这个特性在“归档已删除数据”的场景里很好用先把删除的行插到deleted_users表再删除原表数据。另外特别提醒在 PostgreSQL 中DELETE 大量数据后表空间并不会变小因为旧版本数据还在数据文件里只是对查询不可见。这些“死元组”需要VACUUM回收。日常开发如果只是删了几万行系统会自动触发autovacuum如果删了几百万行建议手动执行VACUUM (VERBOSE, ANALYZE) users;如果你发现一张表删了数据之后文件大小居然一点没变不用慌这是 MVCC 的正常表现不是删除失败了。真正要担心的是经常大批量增删的表膨胀得特别快需要定期维护。5.3 级联删除与外键约束ON DELETE CASCADE 要慎用当表之间有外键关联时删除行为受外键约束的ON DELETE规则影响。比如CREATE TABLE orders ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, amount NUMERIC(10, 2) NOT NULL );如果users表删掉一行orders表里对应的所有订单也会被自动删掉。这对“主子表”场景很方便父删除时子记录理应消失但在用户系统里非常危险你删一个用户他的订单、日志、支付记录全部被级联清除恢复都没法恢复。更稳妥的选择是外键不写ON DELETE CASCADE然后用RESTRICT默认行为删父表时如果还有子记录就直接报错。业务上需要删用户就在代码里显式先处理子表数据再删主表。我个人的原则是外键约束写ON DELETE RESTRICT或NO ACTION级联逻辑由应用层显式实现宁可多写几行代码也不要把销毁数据的决定权交给数据库自动级联。6. 常见问题与排查技巧实录6.1 表名和字段名的大小写问题这是 PG 新手最容易懵的问题。PostgreSQL 对未加引号的标识符会自动折叠成小写。所以CREATE TABLE Users实际创建的表名是users你用SELECT * FROM Users查没问题但用SELECT * FROM Users就会报错“表不存在”。反过来如果你建表时用了双引号CREATE TABLE Users那这个表的真实名字就是大写Users之后每次查询都必须加双引号SELECT * FROM Users。我的建议非常明确一律用全小写加下划线的命名方式别给自己找麻烦。字段名同理userName这种驼峰字段在 PG 里会被折叠成username跟预期不一致时排查起来特费劲。6.2 连接失败password authentication failed for user这个报错太常见了。一般有这么几个原因密码确实错了pg_hba.conf里认证方式不对或者你用了错误的用户去连。排查思路是这样的编辑pg_hba.conf文件找到类似host all all 127.0.0.1/32 scram-sha-256的行如果你的客户端期望用md5认证但服务端只接受scram-sha-256密码的哈希方式不同登录就会失败。改法是把认证方式改成scram-sha-256或md5重新加载配置# 改完 pg_hba.conf 后重新加载配置不用重启服务 pg_ctl reload -D /path/to/your/data/directory另外千万注意修改pg_hba.conf之后不 reload配置是不生效的。很多“改了没用”的问题都是因为这个。6.3 查询慢没有走上索引开发环境数据量小全表扫描也没感觉但当数据量上了百万级不带索引的条件查询就会明显变慢。用EXPLAIN可以看 SQL 的执行计划EXPLAIN ANALYZE SELECT * FROM users WHERE email zhangsanexample.com;看输出里有没有Seq Scan全表扫描如果有而你经常用email做查询条件就应该建索引CREATE INDEX idx_users_email ON users(email);EXPLAIN ANALYZE这个工具是 PG 性能排查的黄金法宝比盲目加索引有依据得多。我在遇到“查询慢”的问题时第一步永远是EXPLAIN而不是猜。6.4 误操作后的紧急自救事务回滚与备份这个算是我私藏的保命技巧。PostgreSQL 的 DML 语句INSERT / UPDATE / DELETE默认是放在隐式事务里执行的也就是说执行完就自动提交了。如果你在命令行psql里操作完全可以手动控制事务BEGIN; DELETE FROM users WHERE age 18; -- 先别提交SELECT 一下确认删对没有 SELECT COUNT(*) FROM users WHERE age 18; -- 如果发现删多了立即回滚 ROLLBACK;做事后补救比事后找备份恢复快得多。在psql里养成习惯批量增删改之前先BEGIN确认无误再COMMIT。这个习惯帮我避免过至少两次在线事故。如果你用 Python 的 psycopg2、Java 的 JDBC、Node 的 pg 库也要在代码层面明确事务边界别让驱动自动提交。再补一个技巧psql里可以用\set AUTOCOMMIT off关闭自动提交模式这样每条 SQL 都会留在事务里需要手动COMMIT;才会真正生效。对初学者来说这个设置能挡住一大半“一不小心全表改了”的麻烦。7. 实操心得增删改查之外的一些经验单纯掌握语法只是第一步真正实用的东西往往在语法之外。我在实际项目里体会最深的一点是PostgreSQL 的方言细节真的值得花时间系统过一遍。比如RETURNING子句几乎每个写操作都值得用它让你不用为了拿一个 ID 多打一次数据库ON CONFLICT让幂等写入变得简单可靠EXPLAIN ANALYZE则让所有性能问题都有迹可循。这些特性组合在一起感觉不像是在用一个普通的数据库而是一个对开发者非常友好的工具集。另外想提醒的一点是SQL 里没有银弹增删改查的“性能问题”往往是在数据模型设计阶段就注定的。比如你的表没有主键、外键缺索引、字段类型用错把数值存成字符串那后面怎么写 SQL 都别扭。所以不要只盯着四条语句本身设计表结构时多想想以后会怎么查这张表往往能避开很多性能地雷。最后关于资料查询PostgreSQL 官方文档写得算数据库里非常详尽的那种遇到不确定的语法查官方文档比在网上搜一堆过时博客靠谱得多。版本注意中文社区的很多教程停留在 9.x、10.x 时代写的SERIAL和md5认证拿到 16、17 版本里已经不太适用了识别信息有没有过时看它有没有提GENERATED AS IDENTITY和SCRAM-SHA-256基本就能判断个大概。