在执行查询或比较时，请务必注意 PostgreSQL 中文本数据的大小写敏感性，以确保结果符合您的预期。大小写还影响数据库执行排序操作的方式。默认情况下，PostgreSQL 在排序或比较字符串值时区分大小写。例如，PostgreSQL 认为“amazon”和“Amazon”是不同的值，这会影响它们的排序方式。
如果要搜索特定值而不考虑其大小写，则需要使用不区分大小写的比较函数或将数据规范化为一致的大小写。PostgreSQL 不区分大小写，在处理数据时具有更大的灵活性和易用性。
在这篇文章中，我们讨论处理 PostgreSQL 中不区分大小写数据的不同选项及其用例、优缺点。
解决方案概述
PostgreSQL 中的不区分大小写可以提高工作效率、减少错误，并更轻松地以灵活且一致的方式处理数据和标识符。然而，需要考虑一些性能权衡。PostgreSQL 提供了不同的选项来实现数据不区分大小写。我们在这篇文章中讨论以下选项：
- 使用不区分大小写的比较函数
- 使用
ILIKEor~~*运算符进行比较 - 使用
CITEXT数据类型 - 使用自定义
=（等于）运算符 - 使用不区分大小写的 ICU 排序规则
选项 1：使用不区分大小写的比较函数
在本选项中，我们讨论 PostgreSQL 内置比较函数LOWER和UPPER. 这些函数允许您在运行时分别将字符串转换为小写或大写。
该lower函数接受一个字符串作为参数，并返回一个所有字符均为小写的新字符串。例如：
<span style="color:#333333"><code class="language-sql">postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#7d8b99"># SELECT pg_catalog.lower('Print IN LOWER');</span>
lower
<span style="color:#7d8b99">----------------</span>
<span style="color:#1990b8">print</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">in</span></span> lower
<span style="color:#5f6364">(</span><span style="color:#c92c2c">1</span> <span style="color:#1990b8">row</span><span style="color:#5f6364">)</span></code></span>upper另一方面，该函数接受一个字符串作为参数，并返回一个所有字符均为大写的新字符串。例如：
<span style="color:#333333"><code class="language-sql">postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#7d8b99"># SELECT pg_catalog.upper('Print in upper');</span>
upper
<span style="color:#7d8b99">----------------</span>
<span style="color:#1990b8">PRINT</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">IN</span></span> UPPER
<span style="color:#5f6364">(</span><span style="color:#c92c2c">1</span> <span style="color:#1990b8">row</span><span style="color:#5f6364">)</span></code></span>这是处理 PostgreSQL 中不区分大小写的直接方法。在文本列上执行搜索时，您可以使用 lower 或 upper 函数将搜索词转换为小写或大写，然后在小写或大写的文本列中搜索该词。这可确保搜索不区分大小写，因此无论文本数据的大小写如何，您都可以找到匹配项。此外，从外部源导入数据时，通常使用lower和upper函数来标准化文本数据的大小写。这有助于确保所有文本数据均采用相同的大小写格式，从而更易于比较和操作。
好处
该选项提供内置功能并且易于使用。
缺点
如果要使用索引进行不区分大小写的搜索或比较，则需要创建专门的索引（例如表达式索引）或专门的数据类型，citext而不是依赖标准文本索引。此外，如果表达式不存在，PostgreSQL 就无法使用表达式索引，因此您需要添加另一个索引以进行区分大小写的搜索。完成以下步骤：
- 创建测试表：
CREATE TABLE public.case_test_opt1 ( id SERIAL PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT NOT NULL ); - 插入一些示例数据。以下查询插入 100 万条记录：
INSERT INTO public.case_test_opt1 (first_name, last_name, email) SELECT 'Foo', 'Bar' || n::text, 'foo.bar' || n::text || '@example.com' FROM generate_series(1, 1000) n; - 在电子邮件列上创建常规索引：
CREATE INDEX case_opt1_email_idx ON public.case_test_opt1 (email); - 运行以下查询，该查询不使用上一步中创建的索引，因为它是普通索引。您可以看到突出显示的全表扫描（seq 扫描）：
postgres=> EXPLAIN ANALYZE SELECT * FROM public.case_test_opt1 WHERE pg_catalog.UPPER(email) = 'FOO.BAR500@EXAMPLE.COM'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..16084.00 rows=5000 width=37) (actual time=0.428..171.612 rows=1000 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on case_test_opt1 (cost=0.00..14584.00 rows=2083 width=37) (actual time=0.237..163.207 rows=333 loops=3) Filter: (upper(email) = 'FOO.BAR500@EXAMPLE.COM'::text) Rows Removed by Filter: 333000 Planning Time: 0.082 ms Execution Time: 171.681 ms (8 rows) - 在大写的电子邮件列上创建功能索引：
CREATE INDEX case_opt1_email_upper_idx ON public.case_test_opt1 (pg_catalog.UPPER(email)); - 运行相同的查询来查看是否使用了功能索引：
<span style="color:#333333"><code class="language-sql">postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">></span></span> <span style="color:#1990b8">EXPLAIN</span> <span style="color:#1990b8">ANALYZE</span>
<span style="color:#1990b8">SELECT</span>
<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">*</span></span>
<span style="color:#1990b8">FROM</span>
<span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt1
<span style="color:#1990b8">WHERE</span>
pg_catalog<span style="color:#5f6364">.</span>UPPER<span style="color:#5f6364">(</span>email<span style="color:#5f6364">)</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span> <span style="color:#2f9c0a">'FOO.BAR500@EXAMPLE.COM'</span><span style="color:#5f6364">;</span>
QUERY <span style="color:#1990b8">PLAN</span>
<span style="color:#7d8b99">------------------------------------------------------------------------------------------------------------------------------------------</span>
Bitmap Heap Scan <span style="color:#1990b8">on</span> case_test_opt1 <span style="color:#5f6364">(</span>cost<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">139.18</span><span style="color:#5f6364">.</span><span style="color:#c92c2c">.7761</span><span style="color:#c92c2c">.06</span> <span style="color:#1990b8">rows</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">5000</span> width<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">37</span><span style="color:#5f6364">)</span> <span style="color:#5f6364">(</span>actual <span style="color:#1990b8">time</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">0.331</span><span style="color:#5f6364">.</span><span style="color:#c92c2c">.1</span><span style="color:#c92c2c">.314</span> <span style="color:#1990b8">rows</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">1000</span> loops<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">1</span><span style="color:#5f6364">)</span>
Recheck Cond: <span style="color:#5f6364">(</span>upper<span style="color:#5f6364">(</span>email<span style="color:#5f6364">)</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span> <span style="color:#2f9c0a">'FOO.BAR500@EXAMPLE.COM'</span>::<span style="color:#1990b8">text</span><span style="color:#5f6364">)</span>
Heap Blocks: exact<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">1000</span>
<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">-</span></span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">></span></span> <strong>Bitmap <span style="color:#1990b8">Index</span> Scan <span style="color:#1990b8">on</span> case_opt1_email_upper_idx</strong> <span style="color:#5f6364">(</span>cost<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">0.00</span><span style="color:#5f6364">.</span><span style="color:#c92c2c">.137</span><span style="color:#c92c2c">.93</span> <span style="color:#1990b8">rows</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">5000</span> width<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">0</span><span style="color:#5f6364">)</span> <span style="color:#5f6364">(</span>actual <span style="color:#1990b8">time</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">0.200</span><span style="color:#5f6364">.</span><span style="color:#c92c2c">.0</span><span style="color:#c92c2c">.200</span> <span style="color:#1990b8">rows</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">1000</span> loops<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">1</span><span style="color:#5f6364">)</span>
<span style="color:#1990b8">Index</span> Cond: <span style="color:#5f6364">(</span>upper<span style="color:#5f6364">(</span>email<span style="color:#5f6364">)</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span> <span style="color:#2f9c0a">'FOO.BAR500@EXAMPLE.COM'</span>::<span style="color:#1990b8">text</span><span style="color:#5f6364">)</span>
Planning <span style="color:#1990b8">Time</span>: <span style="color:#c92c2c">0.149</span> ms
<strong> Execution <span style="color:#1990b8">Time</span>: <span style="color:#c92c2c">1.375</span> ms</strong>
<span style="color:#5f6364">(</span><span style="color:#c92c2c">7</span> <span style="color:#1990b8">rows</span><span style="color:#5f6364">)</span></code></span>选项 2：使用ILIKEor~~*运算符进行比较
在本选项中，我们讨论 PostgreSQL 内置字符串比较运算符ILIKE和~~*。它们在功能上是等效的，但ILIKE是符合标准 SQL 的运算符，而~~*是 PostgreSQL 特定的运算符。
该ILIKE运算符匹配两个字符串，忽略字母大小写的差异。它相当于LIKE运算符，但不区分大小写。例如，表达式的'hello' ILIKE 'HelLO'计算结果为TRUE：
<span style="color:#333333"><code class="language-sql">postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#7d8b99"># SELECT 'hello' ILIKE 'HelLO';</span>
?<span style="color:#1990b8">column</span>?
<span style="color:#7d8b99">----------</span>
t
<span style="color:#5f6364">(</span><span style="color:#c92c2c">1</span> <span style="color:#1990b8">row</span><span style="color:#5f6364">)</span></code></span>该~~*运算符是 PostgreSQL 特定版本ILIKE，并执行类似于 的不区分大小写的字符串比较ILIKE。例如，表达式 'hello' ~~* 'HelLO'将计算为TRUE：
<span style="color:#333333"><code class="language-sql">postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#7d8b99"># SELECT 'hello' ~~* 'HelLO';</span>
?<span style="color:#1990b8">column</span>?
<span style="color:#7d8b99">----------</span>
t
<span style="color:#5f6364">(</span><span style="color:#c92c2c">1</span> <span style="color:#1990b8">row</span><span style="color:#5f6364">)</span></code></span>在搜索表单或过滤器中接受用户输入时，您可以使用ILIKEor~~*运算符以不区分大小写的方式搜索匹配记录。这可确保用户无论输入的大小写如何都可以找到他们要查找的记录。在大型数据集中搜索特定文本模式时，您还可以使用这些运算符。这对于查找包含某个关键字的所有产品名称（无论关键字大小写）等场景非常有用。
好处
该选项提供内置运算符并且易于使用。
缺点
不能将 B 树索引（甚至表达式索引）与ILIKEor~~*运算符一起使用。但是，您可以使用 PostgreSQL 的 GIN 或 GiST 索引类型为这些类型的运算符构建索引。有关 GIN 和 GiST 索引（包括差异）的更多详细信息，请参阅文本搜索的首选索引类型文档。请记住以下几点：
- 您必须使用
pg_trgm扩展程序。此扩展在 Amazon Aurora 和 Amazon RDS 中可用，但默认情况下不启用。CREATE EXTENSION pg_trgm;您可以使用“ ”将其安装到数据库中 - GIN 索引可能比 B 树索引大得多，特别是对于具有许多不同值或长文本字段的数据类型。这可能会影响索引更新和维护的速度。创建 GIN 索引以及插入和更新可能比使用 B 树索引花费更长的时间。
- GIN 索引针对某些查询模式进行了优化，例如在索引数据中搜索多个值或范围。但是，它们对于其他类型的查询可能不那么有效，例如单个列上的相等或范围查询。
使用我们之前创建的同一张表（case_test_opt1），我们可以创建普通索引、功能索引和 GIN 索引，以查看索引创建时间差异：
<span style="color:#333333"><code class="language-sql"><span style="color:#1990b8">CREATE</span> <span style="color:#1990b8">INDEX</span> case_opt1_email_tgrm_idx
<span style="color:#1990b8">ON</span> <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt1 <span style="color:#1990b8">using</span> GIN <span style="color:#5f6364">(</span>email gin_trgm_ops<span style="color:#5f6364">)</span><span style="color:#5f6364">;</span></code></span>对于该数据集，GIN 索引创建时间几乎是上一个示例中 B 树索引的三倍。
以下查询显示 PostgreSQL 在使用 ILIKE 运算符时扫描 GIN 索引：
<span style="color:#333333"><code class="language-sql">postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">></span></span> <span style="color:#1990b8">EXPLAIN</span> <span style="color:#1990b8">ANALYZE</span>
<span style="color:#1990b8">SELECT</span>
<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">*</span></span>
<span style="color:#1990b8">FROM</span>
<span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt1
<span style="color:#1990b8">WHERE</span>
email ILIKE <span style="color:#2f9c0a">'FOO.BAR500@EXAMPLE%'</span><span style="color:#5f6364">;</span>
QUERY <span style="color:#1990b8">PLAN</span>
<span style="color:#7d8b99">-----------------------------------------------------------------------------------------------------------------------------------------</span>
Bitmap Heap Scan <span style="color:#1990b8">on</span> case_test_opt1 <span style="color:#5f6364">(</span>cost<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">344.77</span><span style="color:#5f6364">.</span><span style="color:#c92c2c">.709</span><span style="color:#c92c2c">.64</span> <span style="color:#1990b8">rows</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">99</span> width<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">37</span><span style="color:#5f6364">)</span> <span style="color:#5f6364">(</span>actual <span style="color:#1990b8">time</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">84.869</span><span style="color:#5f6364">.</span><span style="color:#c92c2c">.86</span><span style="color:#c92c2c">.652</span> <span style="color:#1990b8">rows</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">1000</span> loops<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">1</span><span style="color:#5f6364">)</span>
Recheck Cond: <span style="color:#5f6364">(</span>email <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">~</span></span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">~</span></span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">*</span></span> <span style="color:#2f9c0a">'FOO.BAR500@EXAMPLE%'</span>::<span style="color:#1990b8">text</span><span style="color:#5f6364">)</span>
Heap Blocks: exact<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">1000</span>
<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">-</span></span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">></span></span> Bitmap <span style="color:#1990b8">Index</span> Scan <span style="color:#1990b8">on</span> case_opt1_email_tgrm_idx <span style="color:#5f6364">(</span>cost<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">0.00</span><span style="color:#5f6364">.</span><span style="color:#c92c2c">.344</span><span style="color:#c92c2c">.74</span> <span style="color:#1990b8">rows</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">99</span> width<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">0</span><span style="color:#5f6364">)</span> <span style="color:#5f6364">(</span>actual <span style="color:#1990b8">time</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">84.740</span><span style="color:#5f6364">.</span><span style="color:#c92c2c">.84</span><span style="color:#c92c2c">.740</span> <span style="color:#1990b8">rows</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">1000</span> loops<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">1</span><span style="color:#5f6364">)</span>
<span style="color:#1990b8">Index</span> Cond: <span style="color:#5f6364">(</span>email <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">~</span></span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">~</span></span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">*</span></span> <span style="color:#2f9c0a">'FOO.BAR500@EXAMPLE%'</span>::<span style="color:#1990b8">text</span><span style="color:#5f6364">)</span>
Planning <span style="color:#1990b8">Time</span>: <span style="color:#c92c2c">0.256</span> ms
Execution <span style="color:#1990b8">Time</span>: <span style="color:#c92c2c">86.729</span> ms
<span style="color:#5f6364">(</span><span style="color:#c92c2c">7</span> <span style="color:#1990b8">rows</span><span style="color:#5f6364">)</span></code><code class="language-sql">
</code></span>完成测试后清理：
<span style="color:#333333"><code class="language-sql"><span style="color:#1990b8">DROP</span> <span style="color:#1990b8">TABLE</span> <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt1<span style="color:#5f6364">;</span></code></span>选项 3：使用CITEXT数据类型
citext是 PostgreSQL 中不区分大小写的文本数据类型。它是一个扩展模块，提供内置文本数据类型的不区分大小写的版本。当 citext 用于列时，插入该列的任何字符串值都会自动转换为小写，并且对该列的任何查询都不区分大小写。
要使用，您必须首先使用以下命令在数据库中citext启用扩展：citext
<span style="color:#333333"><code class="language-sql"><span style="color:#1990b8">CREATE</span> EXTENSION <span style="color:#1990b8">IF</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">NOT</span></span> <span style="color:#1990b8">EXISTS</span> citext<span style="color:#5f6364">;</span></code></span>之后，您可以citext使用以下语句创建一个包含列的表：
<span style="color:#333333"><code class="language-sql"><span style="color:#1990b8">CREATE</span> <span style="color:#1990b8">TABLE</span> <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_citext_opt3 <span style="color:#5f6364">(</span>
id <span style="color:#1990b8">INT</span> <span style="color:#1990b8">PRIMARY</span> <span style="color:#1990b8">KEY</span><span style="color:#5f6364">,</span>
name CITEXT
<span style="color:#5f6364">)</span><span style="color:#5f6364">;</span></code></span>如果您要从使用不区分大小写text类型的系统迁移数据，则citext可以通过允许您维护相同的数据格式来简化迁移过程。citext可以帮助防止数据输入错误或数据不一致。使用citext，您无需在查询中使用lower和函数来执行不区分大小写的搜索或比较。upper这可以使您的查询更简单且更易于阅读。
好处
此选项提供了数据类型的内置扩展citext。此外，从具有不区分大小写数据类型的其他数据库进行迁移也很简单。
缺点
此选项的缺点是您无法根据需要执行区分大小写的搜索。另外，如果citext运算符的一侧有一个类型，而text另一侧有一个查询类型，那么它可能无法按预期工作。因此，我们需要小心此类查询。
选项 4：使用自定义=运算符
PostgreSQL 允许用户定义自定义运算符来扩展内置的运算符集。可以定义自定义运算符来执行内置运算符不支持的特定操作，或者为常用操作提供简写符号。在此选项中，我们展示如何创建新的 = 运算符来处理不区分大小写的数据。完成以下步骤：
- 创建一个新模式来定义操作员并向公众授予该模式的必要权限：
CREATE SCHEMA util; GRANT USAGE ON SCHEMA util TO PUBLIC; - 创建一个函数来定义运算符的功能。该函数基本上将左右操作数的大小写更改为小写，以便在不修改查询以使用任何内置函数（如
loweror ）的情况下完成比较upper。CREATE OR REPLACE FUNCTION util.case_ins_cmp(text, text) RETURNS BOOLEAN LANGUAGE sql immutable as $$ select pg_catalog.lower($1) operator(pg_catalog.=) pg_catalog.lower($2) $$; - 向公众授予该功能的必要权限：
GRANT EXECUTE ON FUNCTION util.case_ins_cmp(text,text) TO PUBLIC; - 使用新函数创建运算符：
CREATE OPERATOR util.= ( LEFTARG = TEXT, RIGHTARG = TEXT, PROCEDURE = util.case_ins_cmp); - 测试新的运算符，例如：
CREATE TABLE public.case_test_opt4 ( id SERIAL PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT NOT NULL );INSERT INTO public.case_test_opt4 (first_name, last_name, email) VALUES ('foo', 'bar', 'foo.bar@example.com'), ('foo', 'BAR', 'foo.BAR@EXAMPLE.COM'), ('foo', 'BaR', 'foo.BaR@EXAMPLE.COM'); - 使用last_name查询数据并查看结果：
postgres=# SELECT * FROM public.case_test_opt4 WHERE last_name='bar'; id | first_name | last_name | email ----+------------+-----------+--------------------- 1 | foo | bar | foo.bar@example.com (1 row)根据情况，您只能看到一行。
- 设置
search_path使用util之前的架构pg_catalog并检查结果：postgres=# SET search_path TO util, pg_catalog; SET postgres=# SELECT * FROM public.case_test_opt4 WHERE last_name='bar'; id | first_name | last_name | email ----+------------+-----------+--------------------- 1 | foo | bar | foo.bar@example.com 2 | foo | BAR | foo.BAR@EXAMPLE.COM 3 | foo | BaR | foo.BaR@EXAMPLE.COM (3 rows)
无论大小写，您都可以看到所有三行。
或者，您可以使用使用模式的运算符的绝对路径，而不是设置search_path：
<span style="color:#333333"><code class="language-sql">postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#7d8b99"># SELECT * FROM public.case_test_opt4 WHERE last_name operator(util.=) 'bar';</span>
id <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> first_name <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> last_name <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> email
<span style="color:#7d8b99">----+------------+-----------+---------------------</span>
<span style="color:#c92c2c">1</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> bar <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo<span style="color:#5f6364">.</span>bar<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">@example.com</span></span>
<span style="color:#c92c2c">2</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> BAR <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo<span style="color:#5f6364">.</span>BAR<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">@EXAMPLE.COM</span></span>
<span style="color:#c92c2c">3</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> BaR <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo<span style="color:#5f6364">.</span>BaR<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">@EXAMPLE.COM</span></span>
<span style="color:#5f6364">(</span><span style="color:#c92c2c">3</span> <span style="color:#1990b8">rows</span><span style="color:#5f6364">)</span></code></span>如果需要，您可以使用表达式索引：
<span style="color:#333333"><code class="language-sql">postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#7d8b99"># CREATE INDEX ind_opt4 </span>
<span style="color:#1990b8">ON</span> <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt4<span style="color:#5f6364">(</span>pg_catalog<span style="color:#5f6364">.</span>lower<span style="color:#5f6364">(</span>last_name<span style="color:#5f6364">)</span><span style="color:#5f6364">)</span><span style="color:#5f6364">;</span>
<span style="color:#1990b8">CREATE</span> <span style="color:#1990b8">INDEX</span>
postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#7d8b99"># ANALYZE case_test_opt4;</span>
<span style="color:#1990b8">ANALYZE</span>
postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#7d8b99"># EXPLAIN ANALYZE</span>
<span style="color:#1990b8">SELECT</span>
<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">*</span></span>
<span style="color:#1990b8">FROM</span>
<span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt4
<span style="color:#1990b8">WHERE</span>
last_name OPERATOR <span style="color:#5f6364">(</span>util<span style="color:#5f6364">.</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#5f6364">)</span> <span style="color:#2f9c0a">'bar'</span><span style="color:#5f6364">;</span>
QUERY <span style="color:#1990b8">PLAN</span>
<span style="color:#7d8b99">--------------------------------------------------------------------------------------------------------------------------</span>
<span style="color:#1990b8">Index</span> Scan <span style="color:#1990b8">using</span> ind_opt4 <span style="color:#1990b8">on</span> case_test_opt4 <span style="color:#5f6364">(</span>cost<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">0.42</span><span style="color:#5f6364">.</span><span style="color:#c92c2c">.8</span><span style="color:#c92c2c">.44</span> <span style="color:#1990b8">rows</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">1</span> width<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">40</span><span style="color:#5f6364">)</span> <span style="color:#5f6364">(</span>actual <span style="color:#1990b8">time</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">0.071</span><span style="color:#5f6364">.</span><span style="color:#c92c2c">.0</span><span style="color:#c92c2c">.072</span> <span style="color:#1990b8">rows</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">3</span> loops<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#c92c2c">1</span><span style="color:#5f6364">)</span>
<span style="color:#1990b8">Index</span> Cond: <span style="color:#5f6364">(</span>lower<span style="color:#5f6364">(</span>last_name<span style="color:#5f6364">)</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span> <span style="color:#2f9c0a">'bar'</span>::<span style="color:#1990b8">text</span><span style="color:#5f6364">)</span>
Planning <span style="color:#1990b8">Time</span>: <span style="color:#c92c2c">0.322</span> ms
Execution <span style="color:#1990b8">Time</span>: <span style="color:#c92c2c">0.084</span> ms
<span style="color:#5f6364">(</span><span style="color:#c92c2c">4</span> <span style="color:#1990b8">rows</span><span style="color:#5f6364">)</span></code></span>好处
此选项的好处之一是，如果您选择将架构（在其中创建运算符）添加到lowerand集中，您可能不需要更改应用程序查询来添加任何内置函数（如 and）或upper添加任何运算符（如 or ）它到数据库。~~*ILIKEsearch_path
此外，search_path如果您只希望应用程序的一部分表现得不区分大小写，而不对应用程序进行任何更改，则可以将 设为 开关以使数据不区分大小写。
缺点
您需要将search_path（util之前的架构pg_catalog）添加到应用程序查询或更改数据库以设置search_path：
<span style="color:#333333"><code class="language-sql"><span style="color:#1990b8">ALTER</span> <span style="color:#1990b8">DATABASE</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59"><</span></span>db_name<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">></span></span> <span style="color:#1990b8">SET</span> search_path <span style="color:#1990b8">TO</span> <span style="color:#2f9c0a">'util, pg_catalog'</span><span style="color:#5f6364">;</span></code></span>一般来说，自定义运算符可以成为开发人员和 DBA 扩展 PostgreSQL 功能并使某些操作更简单的强大工具。然而，明智地使用它们并遵循命名、文档和测试的最佳实践以确保它们的一致性和高效性非常重要。
完成测试后清理：
<span style="color:#333333"><code class="language-sql"><span style="color:#1990b8">DROP</span> <span style="color:#1990b8">TABLE</span> <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt4<span style="color:#5f6364">;</span>
<span style="color:#1990b8">DROP</span> OPERATOR util<span style="color:#5f6364">.</span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#5f6364">(</span><span style="color:#1990b8">text</span><span style="color:#5f6364">,</span><span style="color:#1990b8">text</span><span style="color:#5f6364">)</span><span style="color:#5f6364">;</span>
<span style="color:#1990b8">DROP</span> <span style="color:#1990b8">FUNCTION</span> util<span style="color:#5f6364">.</span>case_ins_cmp<span style="color:#5f6364">;</span></code></span>选项 5：使用不区分大小写的 ICU 排序规则
“自定义排序规则”是ICU 排序规则提供程序的一项功能。ICU 通过将排序规则设置定义为语言标记的一部分来定义新的排序规则，从而允许对排序规则行为进行广泛的控制。这些设置可以修改排序规则以满足各种需要。您可以通过管理 Amazon Aurora 和 Amazon RDS 上的 PostgreSQL 中的排序规则更改来了解有关排序规则的更多信息。
以下是使用自定义排序规则来访问不区分大小写的数据的测试用例：
- 使用以下命令创建排序规则：
<span style="color:#333333"><code class="language-sql"><span style="color:#1990b8">CREATE</span> COLLATION <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_insensitive <span style="color:#5f6364">(</span>provider <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span> icu<span style="color:#5f6364">,</span> locale <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span> <span style="color:#2f9c0a">'und-u-ks-level2'</span><span style="color:#5f6364">,</span> <span style="color:#1990b8">deterministic</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span> <span style="color:#c92c2c">false</span><span style="color:#5f6364">)</span><span style="color:#5f6364">;</span></code></span>- 创建表并插入数据：
<span style="color:#333333"><code class="language-sql"><span style="color:#1990b8">CREATE</span> <span style="color:#1990b8">TABLE</span> <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt5 <span style="color:#5f6364">(</span>
id <span style="color:#1990b8">SERIAL</span> <span style="color:#1990b8">PRIMARY</span> <span style="color:#1990b8">KEY</span><span style="color:#5f6364">,</span>
first_name <span style="color:#1990b8">TEXT</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">NOT</span></span> <span style="color:#c92c2c">NULL</span> <span style="color:#1990b8">COLLATE</span> case_insensitive<span style="color:#5f6364">,</span>
last_name <span style="color:#1990b8">TEXT</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">NOT</span></span> <span style="color:#c92c2c">NULL</span> <span style="color:#1990b8">COLLATE</span> case_insensitive<span style="color:#5f6364">,</span>
email <span style="color:#1990b8">TEXT</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">NOT</span></span> <span style="color:#c92c2c">NULL</span> <span style="color:#1990b8">COLLATE</span> case_insensitive
<span style="color:#5f6364">)</span><span style="color:#5f6364">;</span>
<span style="color:#1990b8">INSERT</span> <span style="color:#1990b8">INTO</span> <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt5 <span style="color:#5f6364">(</span>first_name<span style="color:#5f6364">,</span> last_name<span style="color:#5f6364">,</span> email<span style="color:#5f6364">)</span> <span style="color:#1990b8">VALUES</span> <span style="color:#5f6364">(</span><span style="color:#2f9c0a">'foo'</span><span style="color:#5f6364">,</span> <span style="color:#2f9c0a">'bar'</span><span style="color:#5f6364">,</span> <span style="color:#2f9c0a">'foo.bar@example.com'</span><span style="color:#5f6364">)</span><span style="color:#5f6364">,</span> <span style="color:#5f6364">(</span><span style="color:#2f9c0a">'foo'</span><span style="color:#5f6364">,</span> <span style="color:#2f9c0a">'BAR'</span><span style="color:#5f6364">,</span> <span style="color:#2f9c0a">'foo.BAR@EXAMPLE.COM'</span><span style="color:#5f6364">)</span><span style="color:#5f6364">,</span> <span style="color:#5f6364">(</span><span style="color:#2f9c0a">'foo'</span><span style="color:#5f6364">,</span> <span style="color:#2f9c0a">'BaR'</span><span style="color:#5f6364">,</span> <span style="color:#2f9c0a">'foo.BaR@EXAMPLE.COM'</span><span style="color:#5f6364">)</span><span style="color:#5f6364">;</span></code></span>- 选择数据并检查结果：
<span style="color:#333333"><code class="language-sql">postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">></span></span> <span style="color:#1990b8">SELECT</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">*</span></span> <span style="color:#1990b8">FROM</span> <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt5 <span style="color:#1990b8">WHERE</span> last_name<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#2f9c0a">'bar'</span><span style="color:#5f6364">;</span>
id <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> first_name <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> last_name <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> email
<span style="color:#7d8b99">----+------------+-----------+---------------------</span>
<span style="color:#c92c2c">1</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> bar <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo<span style="color:#5f6364">.</span>bar<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">@example.com</span></span>
<span style="color:#c92c2c">2</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> BAR <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo<span style="color:#5f6364">.</span>BAR<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">@EXAMPLE.COM</span></span>
<span style="color:#c92c2c">3</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> BaR <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo<span style="color:#5f6364">.</span>BaR<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">@EXAMPLE.COM</span></span>
<span style="color:#5f6364">(</span><span style="color:#c92c2c">3</span> <span style="color:#1990b8">rows</span><span style="color:#5f6364">)</span></code></span>无论大小写，您都可以看到所有三行。
好处
与选项 4 一样，您不需要更改应用程序查询来添加任何内置函数（如loweror ）upper或添加任何运算符（如~~*or ）ILIKE。另外，您不需要设置search_path.
缺点
主要缺点是模式匹配不适用于不区分大小写的非确定性排序规则。从 PostgreSQL 15 开始，这也没有解决索引不确定性排序规则的能力。此外，数据的排序也可能根据您使用的排序规则而改变。如果要更改排序规则，则必须处理排序问题。例如，以下命令的顺序last_name为bar, BAR, BaR：
<span style="color:#333333"><code class="language-sql">postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">></span></span> <span style="color:#1990b8">SELECT</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">*</span></span> <span style="color:#1990b8">FROM</span> <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt5 <span style="color:#1990b8">WHERE</span> last_name<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="color:#2f9c0a">'bar'</span><span style="color:#5f6364">;</span>
id <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> first_name <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> last_name <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> email
<span style="color:#7d8b99">----+------------+-----------+---------------------</span>
<span style="color:#c92c2c">1</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> bar <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo<span style="color:#5f6364">.</span>bar<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">@example.com</span></span>
<span style="color:#c92c2c">2</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> BAR <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo<span style="color:#5f6364">.</span>BAR<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">@EXAMPLE.COM</span></span>
<span style="color:#c92c2c">3</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> BaR <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo<span style="color:#5f6364">.</span>BaR<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">@EXAMPLE.COM</span></span>
<span style="color:#5f6364">(</span><span style="color:#c92c2c">3</span> <span style="color:#1990b8">rows</span><span style="color:#5f6364">)</span></code></span>如果我们修改列的排序规则并运行查询，我们会观察到列的顺序更改为bar, BaR, BAR：
<span style="color:#333333"><code class="language-sql">postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">></span></span> <span style="color:#1990b8">ALTER</span> <span style="color:#1990b8">TABLE</span> <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt5 <span style="color:#1990b8">ALTER</span> <span style="color:#1990b8">COLUMN</span> last_name <span style="color:#1990b8">TYPE</span> <span style="color:#1990b8">text</span> <span style="color:#1990b8">COLLATE</span> <span style="color:#2f9c0a">"en_US"</span><span style="color:#5f6364">;</span>
<span style="color:#1990b8">ALTER</span> <span style="color:#1990b8">TABLE</span>
<span style="color:#1990b8">Time</span>: <span style="color:#c92c2c">57.316</span> ms
postgres<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">=</span></span><span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">></span></span> <span style="color:#1990b8">SELECT</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">*</span></span> <span style="color:#1990b8">FROM</span> <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt5 <span style="color:#1990b8">ORDER</span> <span style="color:#1990b8">BY</span> last_name<span style="color:#5f6364">;</span>
id <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> first_name <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> last_name <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> email
<span style="color:#7d8b99">----+------------+-----------+---------------------</span>
<span style="color:#c92c2c">1</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> bar <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo<span style="color:#5f6364">.</span>bar<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">@example.com</span></span>
<span style="color:#c92c2c">3</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> BaR <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo<span style="color:#5f6364">.</span>BaR<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">@EXAMPLE.COM</span></span>
<span style="color:#c92c2c">2</span> <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> BAR <span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">|</span></span> foo<span style="color:#5f6364">.</span>BAR<span style="background-color:rgba(255, 255, 255, 0.5)"><span style="color:#a67f59">@EXAMPLE.COM</span></span>
<span style="color:#5f6364">(</span><span style="color:#c92c2c">3</span> <span style="color:#1990b8">rows</span><span style="color:#5f6364">)</span></code></span>您可以看到更改排序规则会更改行的顺序。
尽管 PostgreSQL 中的自定义排序规则可以提供许多好处，但在生产环境中实现它们之前仔细考虑潜在的缺点非常重要。正确的规划、测试和持续维护可以帮助确保自定义排序规则有效地工作，并且不会对数据库性能或兼容性产生负面影响。
完成测试后清理：
<span style="color:#333333"><code class="language-sql"><span style="color:#1990b8">DROP</span> <span style="color:#1990b8">TABLE</span> <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_test_opt5<span style="color:#5f6364">;</span>
<span style="color:#1990b8">DROP</span> COLLATION <span style="color:#1990b8">public</span><span style="color:#5f6364">.</span>case_insensitive<span style="color:#5f6364">;</span></code></span>结论
PostgreSQL 提供了几种处理不区分大小写的选项，每种选项都有自己的优点和缺点。最佳方法取决于应用程序的具体要求以及数据库中存储的数据。在这篇文章中，我们讨论了一些选项及其优缺点。根据您的要求，您可以为您的应用程序选择正确的选项。
扫一扫
300
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
