PostgreSQL是否有简单的大小写比较？
我想替换:
SELECT id, user_name
FROM users
WHERE lower(email) IN (lower('adamB@a.com'), lower('eveA@b.com'));
比如:
SELECT id, user_name
FROM users
WHERE email IGNORE_CASE_IN ('adamB@a.com', 'eveA@b.com');
like和ilike运算符处理单个值(例如like 'adamB@a.com')，但不处理集合.
首先，不要做什么:don't use 100...
create table y
(
id serial not null,
email text not null unique
);
insert into y(email)
values('iSteve.jobs@apple.com') ,('linus.Torvalds@linUX.com');
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['ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com']);
执行计划:
memdb=# explain select * from y where email ilike ANY(ARRAY['ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com']);
QUERY PLAN
----------------------------------------------------------------------------------------
Seq Scan on y (cost=0.00..17.52 rows=1 width=7)
Filter: (email ~~* ANY ('{ISteve.Jobs@Apple.com,Linus.Torvalds@Linux.com}'::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['ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com']));
...正确使用索引:
memdb=# explain select * from y where lower(email) = ANY(lower(ARRAY['ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com']));
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on y (cost=22.60..27.98 rows=10 width=7)
Recheck Cond: (lower(email) = ANY ((lower(('{ISteve.Jobs@Apple.com,Linus.Torvalds@Linux.com}'::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(('{ISteve.Jobs@Apple.com,Linus.Torvalds@Linux.com}'::text[])::text))::text[]))
(4 rows)
或者使用citext数据类型...
create table x
(
id serial not null,
email citext not null unique
);
insert into x(email)
values('iSteve.jobs@apple.com'),('linus.Torvalds@linUX.com');
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['ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com']::citext[]);
...哪怕您没有在表达式上创建索引，它也会正确使用索引(例如，在yyy(下(字段))上创建索引zzz):
memdb=# explain select * from x where email = ANY(ARRAY['ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com']::citext[]);
QUERY PLAN
--------------------------------------------------------------------------------------------------
Bitmap Heap Scan on x (cost=8.57..13.91 rows=2 width=36)
Recheck Cond: (email = ANY ('{ISteve.Jobs@Apple.com,Linus.Torvalds@Linux.com}'::citext[]))
-> Bitmap Index Scan on x_email_key (cost=0.00..8.57 rows=2 width=0)
Index Cond: (email = ANY ('{ISteve.Jobs@Apple.com,Linus.Torvalds@Linux.com}'::citext[]))
如果尚未安装citext字段类型，请运行以下命令:
CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;