24.关于PostgreSQL大小写不敏感的说明

关于PostgreSQL大小写不敏感的说明

目录

• 关于PostgreSQL大小写不敏感的说明
  • 前言
  • 示例演示
  • 创建大小写混合或全大写的表/字段
  • ⚠️ 重要注意事项
  • 最佳实践建议
  • 总结

前言

PostgreSQL 对标识符（如表名、列名、函数名等）的处理规则是：

• 在语法层面是不敏感的，但默认会将所有未加双引号的标识符转换为小写。
• 因此，实际上你看到的效果是：不区分大小写，但内部存储为小写。

核心行为

场景	用户输入	实际存储/解析的标识符
不加双引号	CREATE TABLE MyTable	mytable
不加双引号	SELECT * FROM MYTABLE	mytable
加双引号	CREATE TABLE "MyTable"	MyTable（保留原大小写）
加双引号	SELECT * FROM "MyTable"	MyTable

总结：

• 不加双引号 → 自动转成小写 → 大小写不敏感（因为输入大写、小写、混合最终都变成小写）。
• 加双引号 → 完全保留大小写 → 大小写敏感（必须精确匹配大小写和引号）。

示例演示

-- 不加双引号，实际创建的表名为小写 'users'
CREATE TABLE Users (id INT, Name TEXT);

-- 以下查询都能成功，因为 'users'、'USERS'、'Users' 都被转为小写 'users'
SELECT * FROM users;
SELECT * FROM USERS;
SELECT * FROM Users;

-- 但如果你加双引号创建了一个大小写敏感的表名
CREATE TABLE "UserInfo" (id INT);

-- 则必须使用双引号且大小写完全一致才能访问
SELECT * FROM "UserInfo";   -- 正确
SELECT * FROM userinfo;     -- 错误：找不到表 userinfo
SELECT * FROM "USERINFO";   -- 错误：找不到表 USERINFO

创建大小写混合或全大写的表/字段

必须使用双引号。

-- 全大写的表名
CREATE TABLE "ALL_CAPS_TABLE" (id INT);

-- 大小写混合的字段名
CREATE TABLE "MixedCaseTable" (
    "Id" INT,
    "UserName" TEXT,
    "CreatedAt" TIMESTAMP
);

此后任何对该表或字段的引用都必须：

• 使用双引号
• 保持完全相同的大小写

-- 正确的查询
SELECT "Id", "UserName" FROM "MixedCaseTable";

-- 错误的查询（会被转成小写，导致找不到）
SELECT id, username FROM MixedCaseTable;  -- 错误：列 id 不存在，表 mixedcasetable 不存在

⚠️ 重要注意事项

1.一旦使用双引号，就必须始终使用
如果创建时用了 "MyTable"，那么后续所有 SQL 中都必须写 "MyTable"，不能写 mytable 或 "MYTABLE"。这会导致代码可读性差、容易出错。

2.避免使用双引号定义标识符，除非绝对必要
绝大多数 PostgreSQL 最佳实践建议始终使用小写、无引号的标识符。这样既兼容 SQL 标准，又避免了混淆。

3.双引号标识符与 SQL 保留字的冲突
如果你必须使用保留字（如 "user"、"order"）作为表名或列名，双引号是必需的。但更好的做法是避免使用保留字。

4.数据库迁移时的陷阱
如果从其他数据库（如 Oracle、MySQL 默认配置）迁移到 PostgreSQL，而这些数据库有大小写敏感的标识符，迁移工具往往会自动给所有标识符加双引号，导致后续 SQL 必须严格匹配大小写。这经常引发问题。建议迁移时将标识符统一转为小写。

5.性能无影响
大小写处理仅在解析阶段发生，对运行时性能没有影响。

6.配置不可改变
PostgreSQL 没有全局参数可以关闭标识符的大小写折叠行为。这是硬编码在语法规则中的。

与数据内容的区别

• 标识符大小写：如上所述，不敏感（默认转小写），除非加双引号。
• 字符串内容的大小写：PostgreSQL 是敏感的。即 WHERE name = 'John' 和 WHERE name = 'john' 是不同的。如果需要大小写不敏感的数据比较，可以使用 ILIKE、citext 类型或 lower() 函数。

最佳实践建议

• 始终使用小写字母和下划线命名表、字段、索引等，不要加双引号。
  例如：create table user_account (id int, user_name text);
• 不要依赖双引号来实现“美观”的大小写，这会带来长期维护成本。
• 如果确实需要大小写敏感的标识符（例如与外部系统兼容），请确保所有开发者、所有 SQL 脚本、所有 ORM 配置都严格遵守加引号且大小写精确匹配的规则。

总结

问题	答案
PostgreSQL 默认大小写不敏感？	对标识符而言，是的（因为会转为小写）。
是否将大写默认转成小写？	是的，未加双引号的标识符都会转为小写。
如何创建大小写混合/全大写的表或字段？	使用双引号包裹，如 CREATE TABLE "MyTable" (...)。
注意事项	引用时必须始终加双引号且大小写完全一致；避免不必要的双引号，以免增加复杂性。