Postgres-Case-Sensitive

Postgres 大小写敏感问题

在使用 PostgreSQL 数据库时，大小写敏感是一个重要的配置选项。它决定了在进行字符串比较和查询时，是否区分大小写。本文将详细介绍如何配置和查询 PostgreSQL 的大小写敏感设置。

1. 什么是大小写敏感

在数据库中，大小写敏感指的是在执行字符串比较和查询时，是否区分字符串的大小写。在大小写不敏感的情况下，字符串的大小写不会影响查询结果，例如 abc 和 ABC 被视为相同的字符串。而在大小写敏感的情况下， abc 和 ABC 被视为不同的字符串。

2. 默认大小写敏感设置

在默认情况下，PostgreSQL 是大小写敏感的。这意味着在查询时，将严格区分字符串的大小写。
例如，我们创建一个名为 users 的表，并插入一些数据：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO users (name) VALUES ('John');
INSERT INTO users (name) VALUES ('john');

现在，我们执行以下查询语句：

SELECT * FROM users WHERE name = 'John';

结果将只返回一个行，因为只有一个 name 字段的值与 John 完全匹配。

而如果我们执行以下查询语句：

SELECT * FROM users WHERE name = 'john';

结果将返回两个行，因为两个name字段的值都与john完全匹配。

3. 修改大小写敏感设置

如果我们需要修改 PostgreSQL 的大小写敏感设置，可以通过修改配置文件或使用 ALTER SYSTEM 命令来实现。

3.1 修改配置文件

在 PostgreSQL 中，可以使用配置文件 postgresql.conf 对数据库进行配置。我们可以找到该文件的位置，并使用文本编辑器打开它。

一般来说，postgresql.conf 文件位于 PostgreSQL 的 data 目录下。你可以使用以下命令找到该目录：

SHOW config_file;

打开 postgresql.conf 文件后，搜索以下项：

# Case folding can be controlled by this setting.  A value of off disables
# case-folding, causing the server to use its default behavior (which may
# depend on locale settings).  A value of on enables folding of unquoted
# identifiers into lower case.  A value of intermediate (the default) enables
# intermediate behavior: folding of unquoted identifiers into lower case
# unless they match any keyword, and folding of quoted identifiers into lower
# case.  This option is orthogonal to the behavior of the SQL interpreter;
# thus, even with this option enabled, you must still quote caSe-sensitive
# names according to the conventions of the server's SQL dialect.  However,
# this option makes it easier to port applications from other database systems
# that enforce a more restrictive case-folding behavior.  See Section 9.2 for
# more information.
# 
#search_path = '"$user", public'    # schema names
; case_sensitive_names = on

将 case_sensitive_names 的值修改为 off，然后保存并关闭文件。

接下来，重启PostgreSQL服务器，以使配置更改生效。你可以使用以下命令来重启：

sudo systemctl restart postgresql

现在，PostgreSQL将不再区分字符串的大小写。

3.2 使用ALTER SYSTEM命令

除了修改配置文件外，我们还可以使用ALTER SYSTEM命令在运行时修改大小写敏感设置。
首先，连接到数据库，并执行以下命令：

ALTER SYSTEM SET case_sensitive_names = 'off';

然后，重新加载配置文件，以使更改生效：

SELECT pg_reload_conf();

现在，PostgreSQL的大小写敏感设置已被修改。

4. 查询大小写敏感设置

要查询PostgreSQL的大小写敏感设置，可以使用以下SQL语句：

SHOW case_sensitive_names;

该查询将返回当前大小写敏感设置的值。如果返回的值为off，则表示大小写敏感已被禁用。
示例：

SHOW case_sensitive_names;

查询结果：

case_sensitive_names
---------------------
 off
(1 row)

5. 建库时参数

PostgreSQL默认情况下，标识符（如数据库名称、表名称、列名称等）是大小写敏感的。但是，如果在创建数据库时使用了locale设置，例如使用了initdb时带有 --locale=C 选项，或者在 postgresql.conf 中设置了 lc_collate 和 lc_ctype 为 C 或 en_US.UTF-8，这样就可以让 PostgreSQL 在 Windows 上或某些其他操作系统表现出大小写不敏感的特性。
如果你想要确保 PostgreSQL 在所有平台上都表现出大小写不敏感的特性，你可以在创建数据库时使用 initdb 并指定 --locale=C ，或者在数据库运行时修改 postgresql.conf 文件中相关的配置。

例如，在postgresql.conf中设置：

lc_collate = 'C'
lc_ctype = 'C'

在这之后，重启PostgreSQL服务使配置生效。

请注意，即使在设置了lc_ctype=’C’的情况下，SQL标识符仍然是大小写敏感的。只有当你在SQL查询中使用双引号包围标识符时，PostgreSQL才会忽略大小写。例如：

SELECT * FROM "MyTable";  
-- 在这里，大小写不再重要
SELECT * FROM mytable;    
-- 在这里，大小写是重要的

如果你想要确保在没有特殊配置的情况下，也能够让标识符不区分大小写，那么你需要在引用标识符时总是使用双引号。

6. 小结

本文详细介绍了PostgreSQL中大小写敏感的配置和查询方法。通过修改配置文件或使用ALTER SYSTEM命令，我们可以控制PostgreSQL在执行字符串比较和查询时是否区分大小写。这对于应用程序的开发和数据处理非常重要，特别是在需要进行严格的字符串匹配和比较时。