PostgreSQL：不区分大小写的字符串比较 [英] PostgreSQL: Case insensitive string comparison

问题描述

PostgreSQL是否有简单的忽略大小写比较？

Is there a simple ignore-case-comparison for PostgreSQL?

我要替换：

SELECT id, user_name 
    FROM users 
        WHERE lower(email) IN (lower('[e-mail]'), lower('[e-mail]'));

使用类似这样的东西：

SELECT id, user_name 
    FROM users 
        WHERE email IGNORE_CASE_IN ('[e-mail]', '[e-mail]');

像的和 ilike 运算符只处理单个值（例如 ），但不处理集合。

The like and ilike operators work on single values (e.g. like '[e-mail]'), but not on sets.

推荐答案

首先，不要做什么，不要使用ilike ...

First, what not to do, don't use ilike...

create table y
(
id serial not null,
email text not null unique
);

insert into y(email) 
values('[e-mail]') ,('[e-mail]');
insert into y(email) 
select n from generate_series(1,1000) as i(n);

-- no need to create an index on email, 
-- UNIQUE constraint on email already makes an index.
-- thanks a_horse_with_no_name
-- create index ix_y on y(email);

explain select * from y 
where email ilike 
    ANY(ARRAY['[e-mail]','[e-mail]']);

执行计划：

memdb=# explain select * from y where email ilike ANY(ARRAY['[e-mail]','[e-mail]']);
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Seq Scan on y  (cost=0.00..17.52 rows=1 width=7)
   Filter: (email ~~* ANY ('{[e-mail],[e-mail]}'::text[]))
(2 rows)

您可以创建索引的较低表达式...

It's either you create an indexed lower expression...

create function lower(t text[]) returns text[]
as
$$
select lower($1::text)::text[]
$$ language sql;

create unique index ix_y_2 on y(lower(email));

explain select * from y 
where lower(email) = 
    ANY(lower(ARRAY['[e-mail]','[e-mail]']));

...正确使用索引：

...which properly uses index:

memdb=# explain select * from y where lower(email) = ANY(lower(ARRAY['[e-mail]','[e-mail]']));
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on y  (cost=22.60..27.98 rows=10 width=7)
   Recheck Cond: (lower(email) = ANY ((lower(('{[e-mail],[e-mail]}'::text[])::text))::text[]))
   ->  Bitmap Index Scan on ix_y_2  (cost=0.00..22.60 rows=10 width=0)
         Index Cond: (lower(email) = ANY ((lower(('{[e-mail],[e-mail]}'::text[])::text))::text[]))
(4 rows)

或者您使用citext数据类型...

Or you use citext data type...

create table x
(
id serial not null,
email citext not null unique
);

insert into x(email) 
values('[e-mail]'),('[e-mail]');
insert into x(email) 
select n from generate_series(1,1000) as i(n);

-- no need to create an index on email, 
-- UNIQUE constraint on email already makes an index.
-- thanks a_horse_with_no_name
-- create index ix_x on x(email);

explain select * from x 
where email = 
ANY(ARRAY['[e-mail]','[e-mail]']::citext[]);

...即使您未在表达式上创建索引（例如，创建索引），也可以正确使用索引zzy yyy（lower（field）））：

...which properly uses index even you don't create an index on expression (e.g. create index zzz on yyy(lower(field))):

memdb=# explain select * from x where email = ANY(ARRAY['[e-mail]','[e-mail]']::citext[]);
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
Bitmap Heap Scan on x  (cost=8.57..13.91 rows=2 width=36)
  Recheck Cond: (email = ANY ('{[e-mail],[e-mail]}'::citext[]))
  ->  Bitmap Index Scan on x_email_key  (cost=0.00..8.57 rows=2 width=0)
        Index Cond: (email = ANY ('{[e-mail],[e-mail]}'::citext[]))

如果 citext 尚未安装字段类型，请运行以下命令：

If citext field type is not yet installed, run this:

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;

这篇关于PostgreSQL：不区分大小写的字符串比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！