sql - PostgreSQL:不区分大小写的字符串比较

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

我要替换:

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

用类似的东西:

SELECT id, user_name
    FROM users
        WHERE email IGNORE_CASE_IN ('[email protected]', '[email protected]');

like和ilike运算符仅对单个值(例如like '[email protected]')起作用，而不对集合起作用。

最佳答案

首先，不要做什么:不要使用ILIKE ...

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

insert into y(email)
values('[email protected]') ,('[email protected]');
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['[email protected]','[email protected]']);

执行计划:

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

要么创建一个索引较低的表达式...

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['[email protected]','[email protected]']));

...正确使用索引:

memdb=# explain select * from y where lower(email) = ANY(lower(ARRAY['[email protected]','[email protected]']));
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on y  (cost=22.60..27.98 rows=10 width=7)
   Recheck Cond: (lower(email) = ANY ((lower(('{[email protected],[email protected]}'::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(('{[email protected],[email protected]}'::text[])::text))::text[]))
(4 rows)

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

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

insert into x(email)
values('[email protected]'),('[email protected]');
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['[email protected]','[email protected]']::citext[]);

...即使没有在表达式上创建索引也可以正确使用索引(例如，在yyy(lower(field))上创建索引zzz):

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

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

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;