原文地址：Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way
原文作者：Andreas ‘ads’ Scherbaum
Christophe Pettus在这里发布了一个有趣的挑战。表面上看起来相等的两个字符串，但如果你让 PostgreSQL 比较它们，它们是不相等的。
现在让我从一个注释开始：Twitter 完全把这个挑战搞砸了。
怎么会这样？虽然这两个字符串在原始中是不同的，但在将其发布到 Twitter 时，字符串是相等的。这其中的乐趣在哪里？
我向 Christophe 询问了原始查询：
INSERT INTO t VALUES (E'Zo\u0065\u0301', E'Zo\u00e9');
您最终会在表格中看到以下文本：
SELECT * FROM t;
a | b
-----+-----
Zoé | Zoé
(1 row)
如果将 UTF-8 字符串转换为十六进制，则会得到“ 0x5a 0x6f 0x65 0xcc 0x81 ”和“ 0x5a 0x6f 0xc3 0xa9 ”。显然它们是不同的。
但是，如果您转换推文中的两个字符串，您会得到“ 0x5a 0x6f 0xc3 0xa9 ”和“ 0x5a 0x6f 0xc3 0xa9 ”。相同的字符串。可怜的推特。
当我发现这个挑战时，检查十六进制值实际上是我的第一个想法。但是，根据我在“PostgreSQL 中的数据类型”和“ PostgreSQL中的高级数据类型”讲座中的经验，我认为即使字符串实际上相等，也应该有可能“解决”这个难题。
我们将深入探讨 PostgreSQL 的真正可扩展性！
在我上一章的“ PostgreSQL 中的高级数据类型”演讲中，我解释了可以使用您自己的数据类型以及相应的函数、运算符和运算符类来扩展 PostgreSQL。让我们好好利用这些知识，让相等的字符串不相等。
查看查询似乎很容易，我需要的是：
- 一种新的数据类型（类似于字符串）
- “=”的新运算符，实际上使字符串不相等
准备工作
为了保存我的常规数据库并且不会意外地搞砸目录，我创建了一个新数据库，其中进行了以下所有测试：
\set ON_ERROR_STOP false
\c postgres
DROP DATABASE IF EXISTS "Zoé";
CREATE DATABASE "Zoé";
\c "Zoé"
\set ON_ERROR_STOP true
\set ECHO queries
CREATE SCHEMA "Zoé";
SET search_path TO "Zoé";
我将新数据库命名为“ Zoé ”——因为我可以。为了让它看起来更漂亮，我还创建了一个“ Zoé ”模式。因为为什么不。这也有助于显示我在哪里使用我自己的函数和对象，因为它们总是以“ Zoé ”进行模式限定。
新数据类型
PostgreSQL 中的一种新数据类型需要几个支持函数，输入和输出，接收和发送。这是最低限度的。
幸运的是，我正在模仿TEXT类型，因此我可以为此重用所有文本函数。
CREATE FUNCTION "Zoé".varchar2_in(cstring)
RETURNS varchar2
AS 'textin'
LANGUAGE internal STRICT;
CREATE FUNCTION "Zoé".varchar2_out(varchar2)
RETURNS cstring
AS 'textout'
LANGUAGE internal STRICT;
CREATE FUNCTION "Zoé".varchar2_recv(internal)
RETURNS varchar2
AS 'textrecv'
LANGUAGE internal STRICT;
CREATE FUNCTION "Zoé".varchar2_send(varchar2)
RETURNS bytea
AS 'textsend'
LANGUAGE internal STRICT;
是时候创建新类型了。如果你认识这个名字，请告诉我…
CREATE TYPE "Zoé".varchar2 (
INTERNALLENGTH = variable,
INPUT = "Zoé".varchar2_in,
OUTPUT = "Zoé".varchar2_out,
RECEIVE = "Zoé".varchar2_recv,
SEND = "Zoé".varchar2_send,
COLLATABLE = true,
CATEGORY = 'S',
PREFERRED = true,
ALIGNMENT = int4,
STORAGE = extended
);
如您所见，这使用了新创建的支持功能，我将其放入我的专用模式中。
此时我已经可以使用这种类型，创建表，插入数据：
CREATE TABLE "Zoé".t (a "Zoé".varchar2,
b "Zoé".varchar2
);
INSERT INTO "Zoé".t VALUES ('Zoé', 'Zoé');
检查内容：
SELECT * FROM "Zoé".t;
a | b
-----+-----
Zoé | Zoé
(1 row)
操作员
但是我还不能比较这两列：
SELECT * FROM "Zoé".t WHERE a = b;
SELECT * FROM "Zoé".t WHERE a = b;
ERROR: operator does not exist: varchar2 = varchar2
LINE 1: SELECT * FROM "Zoé".t WHERE a = b;
^
HINT: No operator matches the given name and argument types. You might need to add
PostgreSQL 不知道如何比较我的新类型的两个字符串。我需要为我的新类型创建“ = ”运算符。
事实上，我不仅需要创建“ = ”运算符，还需要创建“ <> ”运算符，因为“ = ”运算符需要一个否定符。
为了创建这两个运算符，我还需要两个函数来比较我的两个varchar2值，并告诉 PostgreSQL 它们是否相等。这就是变化发生的地方。
TEXT的原始功能：
CREATE FUNCTION pg_catalog.texteq (TEXT, TEXT)
RETURNS boolean
AS 'texteq'
LANGUAGE internal STRICT;
CREATE FUNCTION pg_catalog.textne (TEXT, TEXT)
RETURNS boolean
AS 'textne'
LANGUAGE internal STRICT;
我的新数据类型的功能：
CREATE FUNCTION "Zoé".texteq ("Zoé".varchar2, "Zoé".varchar2)
RETURNS boolean
AS 'textne'
LANGUAGE internal STRICT;
CREATE FUNCTION "Zoé".textne ("Zoé".varchar2, "Zoé".varchar2)
RETURNS boolean
AS 'texteq'
LANGUAGE internal STRICT;
你看出区别了吗？
现在我有了支持函数，我可以创建运算符：
CREATE OPERATOR "Zoé".<> (
FUNCTION = textne,
LEFTARG = "Zoé".varchar2,
RIGHTARG = "Zoé".varchar2,
COMMUTATOR = OPERATOR("Zoé".<>),
NEGATOR = OPERATOR("Zoé".=),
RESTRICT = neqsel,
JOIN = neqjoinsel
);
CREATE OPERATOR "Zoé".= (
FUNCTION = texteq,
LEFTARG = "Zoé".varchar2,
RIGHTARG = "Zoé".varchar2,
COMMUTATOR = OPERATOR("Zoé".=),
NEGATOR = OPERATOR("Zoé".<>),
MERGES,
HASHES,
RESTRICT = eqsel,
JOIN = eqjoinsel
);
测试一下
SELECT * FROM "Zoé".t WHERE a = b;
a | b
---+---
(0 rows)
由于我刚刚切换了运算符，我可以将查询转过来询问字符串是否不相等：
SELECT * FROM "Zoé".t WHERE a != b;
a | b
-----+-----
Zoé | Zoé
(1 row)
最后一句
为我的新类型切换 texteq和 textne函数会告诉 PostgreSQL 字符串不相等，而实际上它们是相等的。另一个同样“很好”的选项是我使用“正确”的内部函数创建texteq和nextne函数，但是在创建运算符时切换这些函数。这将给出相同的结果。
PostgreSQL 的可扩展性让我可以自由地设计我想看到的结果，切换真假，让你相信字符串不相等，而实际上它们是不相等的。
当然，这只是为应对挑战而开发的一个快速示例。然而，它表明 PostgreSQL 确实可以扩展以处理您对应用程序的任何类型的需求。
