主页 / dba / 问题 / 192204
Accepted
Ben Johnson
Asked: 2017-12-02 11:49:33 +0800 CST

PostgreSQL 的 citext 类型如何影响使用 LOWER() 进行的字符串比较？

  • 772

我提出这个问题的动机是，我曾希望 PostgreSQL 在从citext列中进行选择时表现一致，而不管要匹配的字符串是否包含在一个或多个实例中lower()（任何此类包装都超出了我的控制范围）。事实并非如此。（当然，完全有可能我的测试无效或者我误解了基本概念。）

重现测试场景的步骤

CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE users (id int, email citext);
INSERT INTO users(id, email) VALUES
  (1, '[email protected]');

测试

正如使用该citext类型时预期的那样，小写变体会产生结果：

# select * from users where email = '[email protected]';
 id |      email
----+------------------
  1 | [email protected]
(1 row)

=将运算符更改为like产生结果：

select * from users where email like lower('[email protected]');
 id |      email
----+------------------
  1 | [email protected]
(1 row)

与“逆”一样：

# select * from users where lower(email) = '[email protected]';
 id |      email
----+------------------
  1 | [email protected]
(1 row)

将两个值包装在 中也是如此lower()：

# select * from users where lower(email) = lower('[email protected]');
 id |      email
----+------------------
  1 | [email protected]
(1 row)

我的问题

那么为什么下面的查询在此实例中不返回结果呢？

# select * from users where email = lower('[email protected]');
 id | email
----+-------
(0 rows)

手册说citext的类型：

本质上，它在比较值时内部调用较低。

关键词似乎是“本质上”；此声明暗示以下内容，它确实产生了结果：

select * from users where lower(email) = lower(lower('[email protected]'));
 id |      email
----+------------------
  1 | [email protected]
(1 row)

这可能与Limitations上述文件部分中的以下警告有关吗？

citext 的大小写折叠行为取决于数据库的 LC_CTYPE 设置。

# SHOW LC_CTYPE;
  lc_ctype
-------------
 en_US.UTF-8
(1 row)

非常感谢这方面的任何解释。

postgresql postgresql-10

1 个回答

  1. Best Answer

    tldr; 在比较不区分大小写和敏感的事物是否相等时，您必须明确。text明确区分大小写；citext明确不区分大小写。你应该为双方提供演员表并且明确

    关于的几件事lower()

    • lower()被输入
    • 当它的参数是时text，它总是返回text

    其他几点

    • 当您与文字进行比较时，类型是未知的（它在unknown内部是明确的）。
    • 运算符是函数。
    • 函数在运行时强制 PostgreSQL 中的类型。

    在这种情况下，类型如下，带有描述

    -- text = unknown
-- unknown promoted to text, this has nothing to do with citext
lower(email) = '[email protected]';

-- text = text
-- this has nothing to do with citext
lower(email) = lower('[email protected]');

-- text = text
-- this has nothing to do with citext    
lower(email) = lower(lower('[email protected]'));

-- citext LIKE text
-- LIKE is smart `operator ~~(citext,text)` via `texticlike`
-- WORKS
email like lower('[email protected]');

-- citext = unknown
-- unknown promoted to citext, there is an `operator =(citext,citext)`
-- WORKS
email = '[email protected]';

-- citext = text
-- citext promoted to text, there is no `operator =(citext,text)`
-- FAILS
email = lower('[email protected]');

    总之，有一个operator =(citext,citext). 这样你就可以

    email = lower('[email protected]')::citext;

    如果需要，或者您可以定义自己的运算符，设置=为不区分大小写的路由而不是区分大小写的路由。虽然我发现这是一种可怕的做法，但我会一直施法。

    • 2

相关问题