树叶云带你慢慢了解PostgreSQL那些语法细节和实用技巧

整理自PostgreSQL官方文档、PG中文手册、CSDN博主“树叶云”系列分享、知乎专栏“数据库实战笔记”等公开资料）

树叶云之前写过一篇很长的入门指南,里面提到PostgreSQL的语法有时候看起来和别的数据库差不多，但细节上藏着不少容易踩坑的地方，今天我们就慢慢聊几个常见的场景，不讲太理论的东西，就说实际怎么用。

比如最简单的查询,SELECT FROM table，很多人觉得这有啥可讲的？但PostgreSQL里有个细节：表名和字段名如果用了大写字母或者特殊字符，查询的时候必须用双引号包起来，不然会被自动转成小写，比如你建表时写了CREATE TABLE "MyTable" ("ID" int)，那么查询时必须用SELECT FROM "MyTable"，如果写成select * from mytable就会报错说表不存在，这个习惯是从SQL标准来的，但MySQL之类的数据库可能没这么严格，所以从其他数据库迁移过来的人容易在这里栽跟头。（来源：PG中文手册“标识符和关键字”章节）

再说说插入数据时的RETURNING子句,这是PostgreSQL特别实用的一个功能，比如你插入一条记录后想立刻拿到自动生成的主键ID，不需要再执行一次查询，直接写INSERT INTO users (name) VALUES ('张三') RETURNING id; 数据库会直接把新生成的id值返回给你，在业务代码里特别省事，尤其适合需要连续操作多个关联表的场景。（来源：知乎专栏“数据库实战笔记”案例篇）

关于条件判断,PostgreSQL的CASE WHEN和COALESCE用得好能少写很多代码，比如统计用户年龄分布，可以写CASE WHEN age < 18 THEN '未成年' WHEN age BETWEEN 18 AND 60 THEN '成年' ELSE '老年' END，但树叶云提醒过一个细节：如果多个WHEN条件有重叠，PostgreSQL是按顺序匹配的，第一个满足条件就会返回，不会继续往下判断，这点和编程里的if-else逻辑一样，但有些人写复杂条件时容易忘记顺序的重要性。

字符串处理方面,PostgreSQL支持正则表达式匹配，这是很多数据库没有的，比如想找出邮箱地址里带数字的用户，可以用SELECT FROM users WHERE email ~ '[0-9]'。~符号表示正则匹配，~表示不区分大小写匹配，树叶云在某个案例里提到，他们曾用这个功能快速清理过脏数据，比用LIKE '%123%'灵活得多。（来源：CSDN博主“树叶云”《用正则搞定数据清洗》）

时间计算也是日常高频操作,比如要查最近7天的订单，可以写WHERE order_time >= CURRENT_DATE - INTERVAL '7 days'，这里INTERVAL关键字可以灵活加减时间单位，支持year/month/hour甚至更细的精度，还有个容易混淆的点：CURRENT_DATE只返回日期，CURRENT_TIMESTAMP返回带时区的时间，NOW()和CURRENT_TIMESTAMP是等价的，如果业务涉及多时区，最好显式指定时区存储。

联合查询时,JOIN和WHERE的过滤时机不同，树叶云举过一个例子：LEFT JOIN时，条件写在ON后面和写在WHERE后面结果可能不一样，ON条件是在连接时过滤右表，不影响左表记录数量；WHERE条件是在连接完成后对整个结果集过滤，可能会把左表本该保留的记录也过滤掉，这个区别在写统计报表时特别重要，搞错会导致数据遗漏。

最后说一个调试技巧,EXPLAIN命令可以查看查询执行计划，但树叶云建议新手加上ANALYZE参数实际执行一遍（EXPLAIN ANALYZE），能看到实际耗时和返回行数，不过要注意，这个命令会真实执行SQL，在生产环境谨慎使用，如果只是看计划不加ANALYZE，则不会真正跑查询。

这些细节看似零碎,但组合起来就能让日常开发效率提升不少，PostgreSQL的文档很全面，遇到问题多查手册，比盲目搜索更容易找到正确答案。（综合自多个来源的实用技巧汇总）