PostgreSQL: hoofdletterongevoelige tekenreeksvergelijking

Is er een simpele negeer-case-vergelijking voor PostgreSQL?

Ik wil vervangen:

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

Met zoiets als:

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

De operators likeen ILIKEwerken op enkele waarden (bijv. like '[email protected]'), maar niet op sets.


Antwoord 1, autoriteit 100%

select * 
where email ilike '[email protected]'

ILIKElijkt op likemaar is niet hoofdlettergevoelig. Gebruik voor escape-tekens replace()

where email ilike replace(replace(replace($1, '~', '~~'), '%', '~%'), '_', '~_') escape '~'

of je zou een functie kunnen maken om tekst te escapen; voor array van tekst gebruik

where email ilike any(array['[email protected]', '[email protected]'])

Antwoord 2, autoriteit 54%

Ten eerste, wat u niet moet doen: gebruik ILIKEniet…

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]']);

Uitvoeringsplan:

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)

U maakt ofwel een geïndexeerde lagere uitdrukking…

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]']));

…die index correct gebruikt:

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)

Of u gebruikt het gegevenstype 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[]);

…die index correct gebruikt, zelfs als u geen index op expressie maakt (bijv. maak index zzz op yyy(lower(field))):

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[]))

Als het veldtype citextnog niet is geïnstalleerd, voer je dit uit:

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;

Antwoord 3, autoriteit 46%

Er zijn dingen veranderd in de afgelopen 4 jaar sinds deze vraag werd beantwoord en de aanbeveling “gebruik ILIKE niet” is niet meer waar (althans op zo’n algemene manier).

In feite, afhankelijk van de gegevensdistributie, ILIKE met een trigramindexis misschien zelfs sneller dan citext.

Voor een unieke index is er inderdaad een groot verschil, wat te zien is bij gebruik van Michael’s testopstelling:

create table y
(
  id serial not null,
  email text not null unique
);
insert into y(email) 
select 'some.name'||n||'@foobar.com'
from generate_series(1,100000) as i(n);
-- create a trigram index to support ILIKE    
create index ix_y on y using gin (email gin_trgm_ops);
create table x
(
  id serial not null,
  email citext not null unique
);
-- no need to create an index
-- the UNIQUE constraint will create a regular B-Tree index
insert into x(email) 
select email
from y;

Het uitvoeringsplan voor het gebruik van ILIKE:

explain (analyze)
select * 
from y 
where email ilike ANY (ARRAY['[email protected]','[email protected]']);
Bitmap Heap Scan on y  (cost=126.07..154.50 rows=20 width=29) (actual time=60.696..60.818 rows=2 loops=1)
  Recheck Cond: (email ~~* ANY ('{[email protected],[email protected]}'::text[]))
  Rows Removed by Index Recheck: 13
  Heap Blocks: exact=11
  ->  Bitmap Index Scan on ix_y  (cost=0.00..126.07 rows=20 width=0) (actual time=60.661..60.661 rows=15 loops=1)
        Index Cond: (email ~~* ANY ('{[email protected],[email protected]}'::text[]))
Planning time: 0.952 ms
Execution time: 61.004 ms

En voor het gebruik van citext:

explain (analyze)
select * 
from x 
where email = ANY (ARRAY['[email protected]','[email protected]']);
Index Scan using x_email_key on x  (cost=0.42..5.85 rows=2 width=29) (actual time=0.111..0.203 rows=2 loops=1)
  Index Cond: (email = ANY ('{[email protected],[email protected]}'::citext[]))
Planning time: 0.115 ms
Execution time: 0.254 ms

Merk op dat de ILIKE-query in feite iets anders is dan de =-query voor citext, aangezien ILIKE jokertekens zou respecteren.


Voor een niet-unieke index ziet het er echter anders uit. De volgende opzet is gebaseerd op een recente vraagdie dezelfde vraag stelt:

create table data
(
  group_id serial primary key,
  name text
);
create table data_ci
(
  group_id serial primary key,
  name citext
);
insert into data(name)
select 'data'||i.n
from generate_series(1,1000) as i(n), generate_series(1,1000) as i2(n);
insert into data_ci(group_id, name)
select group_id, name
from data;
create index ix_data_gin on data using gin (name public.gin_trgm_ops);
create index ix_data_ci on data_ci (name);

We hebben dus een miljoen rijen in elke tabel en 1000 afzonderlijke waarden voor de kolom nameen voor elke afzonderlijke waarde hebben we 1000 dubbele waarden. Een zoekopdracht op zoek naar 3 verschillende waarden zal dus 3000 rijen opleveren.

In dit geval is de trigram-index aanzienlijk sneller dan de Btree-index:

explain (analyze)
select *
from data 
where name ilike any (array['Data1', 'data2', 'DATA3']);
Bitmap Heap Scan on data  (cost=88.25..1777.61 rows=1535 width=11) (actual time=2.906..11.064 rows=3000 loops=1)
  Recheck Cond: (name ~~* ANY ('{Data1,data2,DATA3}'::text[]))
  Heap Blocks: exact=17
  ->  Bitmap Index Scan on ix_data_gin  (cost=0.00..87.87 rows=1535 width=0) (actual time=2.869..2.869 rows=3000 loops=1)
        Index Cond: (name ~~* ANY ('{Data1,data2,DATA3}'::text[]))
Planning time: 2.174 ms
Execution time: 11.282 ms

En de btree-index in de citext-kolom gebruikt nu een Seq Scan

explain analyze
select *
from data_ci
where name = any (array['Data1', 'data2', 'DATA3']);
Seq Scan on data_ci  (cost=0.00..10156.00 rows=2904 width=11) (actual time=0.449..304.301 rows=1000 loops=1)
  Filter: ((name)::text = ANY ('{Data1,data2,DATA3}'::text[]))
  Rows Removed by Filter: 999000
Planning time: 0.152 ms
Execution time: 304.360 ms

Ook de grootte van de GIN-index is eigenlijk kleiner dan die in de citext-kolom:

select pg_size_pretty(pg_total_relation_size('ix_data_gin')) as gin_index_size, 
       pg_size_pretty(pg_total_relation_size('ix_data_ci')) as citex_index_size
gin_index_size | citex_index_size
---------------+-----------------
11 MB          | 21 MB           

Het bovenstaande is gedaan met Postgres 9.6.1 op een Windows-laptop met random_page_costingesteld op 1.5


Antwoord 4, autoriteit 21%

Gebruik hoofdletterongevoelig tekstgegevenstype. Gebruik citext:

create table emails
(
user_id int references users(user_id)
email citext
);
insert into emails(user_id, email) values(1, '[email protected]');
insert into emails(user_id, email) values(2, '[email protected]');
select * from emails where email in ('[email protected]','[email protected]');

Als je de citext.sql niet kunt vinden in je contrib directory, kopieer en plak dit dan in je pgAdmin:

/* $PostgreSQL: pgsql/contrib/citext/citext.sql.in,v 1.3 2008/09/05 18:25:16 tgl Exp $ */
-- Adjust this setting to control where the objects get created.
SET search_path = public;
--
--  PostgreSQL code for CITEXT.
--
-- Most I/O functions, and a few others, piggyback on the "text" type
-- functions via the implicit cast to text.
--
--
-- Shell type to keep things a bit quieter.
--
CREATE TYPE citext;
--
--  Input and output functions.
--
CREATE OR REPLACE FUNCTION citextin(cstring)
RETURNS citext
AS 'textin'
LANGUAGE internal IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION citextout(citext)
RETURNS cstring
AS 'textout'
LANGUAGE internal IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION citextrecv(internal)
RETURNS citext
AS 'textrecv'
LANGUAGE internal STABLE STRICT;
CREATE OR REPLACE FUNCTION citextsend(citext)
RETURNS bytea
AS 'textsend'
LANGUAGE internal STABLE STRICT;
--
--  The type itself.
--
CREATE TYPE citext (
    INPUT          = citextin,
    OUTPUT         = citextout,
    RECEIVE        = citextrecv,
    SEND           = citextsend,
    INTERNALLENGTH = VARIABLE,
    STORAGE        = extended,
    -- make it a non-preferred member of string type category
    CATEGORY       = 'S',
    PREFERRED      = false
);
--
-- Type casting functions for those situations where the I/O casts don't
-- automatically kick in.
--
CREATE OR REPLACE FUNCTION citext(bpchar)
RETURNS citext
AS 'rtrim1'
LANGUAGE internal IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION citext(boolean)
RETURNS citext
AS 'booltext'
LANGUAGE internal IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION citext(inet)
RETURNS citext
AS 'network_show'
LANGUAGE internal IMMUTABLE STRICT;
--
--  Implicit and assignment type casts.
--
CREATE CAST (citext AS text)    WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (citext AS varchar) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (citext AS bpchar)  WITHOUT FUNCTION AS ASSIGNMENT;
CREATE CAST (text AS citext)    WITHOUT FUNCTION AS ASSIGNMENT;
CREATE CAST (varchar AS citext) WITHOUT FUNCTION AS ASSIGNMENT;
CREATE CAST (bpchar AS citext)  WITH FUNCTION citext(bpchar)  AS ASSIGNMENT;
CREATE CAST (boolean AS citext) WITH FUNCTION citext(boolean) AS ASSIGNMENT;
CREATE CAST (inet AS citext)    WITH FUNCTION citext(inet)    AS ASSIGNMENT;
--
-- Operator Functions.
--
CREATE OR REPLACE FUNCTION citext_eq( citext, citext )
RETURNS bool
AS '$libdir/citext'
LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION citext_ne( citext, citext )
RETURNS bool
AS '$libdir/citext'
LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION citext_lt( citext, citext )
RETURNS bool
AS '$libdir/citext'
LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION citext_le( citext, citext )
RETURNS bool
AS '$libdir/citext'
LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION citext_gt( citext, citext )
RETURNS bool
AS '$libdir/citext'
LANGUAGE C IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION citext_ge( citext, citext )
RETURNS bool
AS '$libdir/citext'
LANGUAGE C IMMUTABLE STRICT;
--
-- Operators.
--
CREATE OPERATOR = (
    LEFTARG    = CITEXT,
    RIGHTARG   = CITEXT,
    COMMUTATOR = =,
    NEGATOR    = <>,
    PROCEDURE  = citext_eq,
    RESTRICT   = eqsel,
    JOIN       = eqjoinsel,
    HASHES,
    MERGES
);
CREATE OPERATOR <> (
    LEFTARG    = CITEXT,
    RIGHTARG   = CITEXT,
    NEGATOR    = =,
    COMMUTATOR = <>,
    PROCEDURE  = citext_ne,
    RESTRICT   = neqsel,
    JOIN       = neqjoinsel
);
CREATE OPERATOR < (
    LEFTARG    = CITEXT,
    RIGHTARG   = CITEXT,
    NEGATOR    = >=,
    COMMUTATOR = >,
    PROCEDURE  = citext_lt,
    RESTRICT   = scalarltsel,
    JOIN       = scalarltjoinsel
);
CREATE OPERATOR <= (
    LEFTARG    = CITEXT,
    RIGHTARG   = CITEXT,
    NEGATOR    = >,
    COMMUTATOR = >=,
    PROCEDURE  = citext_le,
    RESTRICT   = scalarltsel,
    JOIN       = scalarltjoinsel
);
CREATE OPERATOR >= (
    LEFTARG    = CITEXT,
    RIGHTARG   = CITEXT,
    NEGATOR    = <,
    COMMUTATOR = <=,
    PROCEDURE  = citext_ge,
    RESTRICT   = scalargtsel,
    JOIN       = scalargtjoinsel
);
CREATE OPERATOR > (
    LEFTARG    = CITEXT,
    RIGHTARG   = CITEXT,
    NEGATOR    = <=,
    COMMUTATOR = <,
    PROCEDURE  = citext_gt,
    RESTRICT   = scalargtsel,
    JOIN       = scalargtjoinsel
);
--
-- Support functions for indexing.
--
CREATE OR REPLACE FUNCTION citext_cmp(citext, citext)
RETURNS int4
AS '$libdir/citext'
LANGUAGE C STRICT IMMUTABLE;
CREATE OR REPLACE FUNCTION citext_hash(citext)
RETURNS int4
AS '$libdir/citext'
LANGUAGE C STRICT IMMUTABLE;
--
-- The btree indexing operator class.
--
CREATE OPERATOR CLASS citext_ops
DEFAULT FOR TYPE CITEXT USING btree AS
    OPERATOR    1   <  (citext, citext),
    OPERATOR    2   <= (citext, citext),
    OPERATOR    3   =  (citext, citext),
    OPERATOR    4   >= (citext, citext),
    OPERATOR    5   >  (citext, citext),
    FUNCTION    1   citext_cmp(citext, citext);
--
-- The hash indexing operator class.
--
CREATE OPERATOR CLASS citext_ops
DEFAULT FOR TYPE citext USING hash AS
    OPERATOR    1   =  (citext, citext),
    FUNCTION    1   citext_hash(citext);
--
-- Aggregates.
--
CREATE OR REPLACE FUNCTION citext_smaller(citext, citext)
RETURNS citext
AS '$libdir/citext'
LANGUAGE 'C' IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION citext_larger(citext, citext)
RETURNS citext
AS '$libdir/citext'
LANGUAGE 'C' IMMUTABLE STRICT;
CREATE AGGREGATE min(citext)  (
    SFUNC = citext_smaller,
    STYPE = citext,
    SORTOP = <
);
CREATE AGGREGATE max(citext)  (
    SFUNC = citext_larger,
    STYPE = citext,
    SORTOP = >
);
--
-- CITEXT pattern matching.
--
CREATE OR REPLACE FUNCTION texticlike(citext, citext)
RETURNS bool AS 'texticlike'
LANGUAGE internal IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION texticnlike(citext, citext)
RETURNS bool AS 'texticnlike'
LANGUAGE internal IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION texticregexeq(citext, citext)
RETURNS bool AS 'texticregexeq'
LANGUAGE internal IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION texticregexne(citext, citext)
RETURNS bool AS 'texticregexne'
LANGUAGE internal IMMUTABLE STRICT;
CREATE OPERATOR ~ (
    PROCEDURE = texticregexeq,
    LEFTARG   = citext,
    RIGHTARG  = citext,
    NEGATOR   = !~,
    RESTRICT  = icregexeqsel,
    JOIN      = icregexeqjoinsel
);
CREATE OPERATOR ~* (
    PROCEDURE = texticregexeq,
    LEFTARG   = citext,
    RIGHTARG  = citext,
    NEGATOR   = !~*,
    RESTRICT  = icregexeqsel,
    JOIN      = icregexeqjoinsel
);
CREATE OPERATOR !~ (
    PROCEDURE = texticregexne,
    LEFTARG   = citext,
    RIGHTARG  = citext,
    NEGATOR   = ~,
    RESTRICT  = icregexnesel,
    JOIN      = icregexnejoinsel
);
CREATE OPERATOR !~* (
    PROCEDURE = texticregexne,
    LEFTARG   = citext,
    RIGHTARG  = citext,
    NEGATOR   = ~*,
    RESTRICT  = icregexnesel,
    JOIN      = icregexnejoinsel
);
CREATE OPERATOR ~~ (
    PROCEDURE = texticlike,
    LEFTARG   = citext,
    RIGHTARG  = citext,
    NEGATOR   = !~~,
    RESTRICT  = iclikesel,
    JOIN      = iclikejoinsel
);
CREATE OPERATOR ~~* (
    PROCEDURE = texticlike,
    LEFTARG   = citext,
    RIGHTARG  = citext,
    NEGATOR   = !~~*,
    RESTRICT  = iclikesel,
    JOIN      = iclikejoinsel
);
CREATE OPERATOR !~~ (
    PROCEDURE = texticnlike,
    LEFTARG   = citext,
    RIGHTARG  = citext,
    NEGATOR   = ~~,
    RESTRICT  = icnlikesel,
    JOIN      = icnlikejoinsel
);
CREATE OPERATOR !~~* (
    PROCEDURE = texticnlike,
    LEFTARG   = citext,
    RIGHTARG  = citext,
    NEGATOR   = ~~*,
    RESTRICT  = icnlikesel,
    JOIN      = icnlikejoinsel
);
--
-- Matching citext to text. 
--
CREATE OR REPLACE FUNCTION texticlike(citext, text)
RETURNS bool AS 'texticlike'
LANGUAGE internal IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION texticnlike(citext, text)
RETURNS bool AS 'texticnlike'
LANGUAGE internal IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION texticregexeq(citext, text)
RETURNS bool AS 'texticregexeq'
LANGUAGE internal IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION texticregexne(citext, text)
RETURNS bool AS 'texticregexne'
LANGUAGE internal IMMUTABLE STRICT;
CREATE OPERATOR ~ (
    PROCEDURE = texticregexeq,
    LEFTARG   = citext,
    RIGHTARG  = text,
    NEGATOR   = !~,
    RESTRICT  = icregexeqsel,
    JOIN      = icregexeqjoinsel
);
CREATE OPERATOR ~* (
    PROCEDURE = texticregexeq,
    LEFTARG   = citext,
    RIGHTARG  = text,
    NEGATOR   = !~*,
    RESTRICT  = icregexeqsel,
    JOIN      = icregexeqjoinsel
);
CREATE OPERATOR !~ (
    PROCEDURE = texticregexne,
    LEFTARG   = citext,
    RIGHTARG  = text,
    NEGATOR   = ~,
    RESTRICT  = icregexnesel,
    JOIN      = icregexnejoinsel
);
CREATE OPERATOR !~* (
    PROCEDURE = texticregexne,
    LEFTARG   = citext,
    RIGHTARG  = text,
    NEGATOR   = ~*,
    RESTRICT  = icregexnesel,
    JOIN      = icregexnejoinsel
);
CREATE OPERATOR ~~ (
    PROCEDURE = texticlike,
    LEFTARG   = citext,
    RIGHTARG  = text,
    NEGATOR   = !~~,
    RESTRICT  = iclikesel,
    JOIN      = iclikejoinsel
);
CREATE OPERATOR ~~* (
    PROCEDURE = texticlike,
    LEFTARG   = citext,
    RIGHTARG  = text,
    NEGATOR   = !~~*,
    RESTRICT  = iclikesel,
    JOIN      = iclikejoinsel
);
CREATE OPERATOR !~~ (
    PROCEDURE = texticnlike,
    LEFTARG   = citext,
    RIGHTARG  = text,
    NEGATOR   = ~~,
    RESTRICT  = icnlikesel,
    JOIN      = icnlikejoinsel
);
CREATE OPERATOR !~~* (
    PROCEDURE = texticnlike,
    LEFTARG   = citext,
    RIGHTARG  = text,
    NEGATOR   = ~~*,
    RESTRICT  = icnlikesel,
    JOIN      = icnlikejoinsel
);
--
-- Matching citext in string comparison functions.
-- XXX TODO Ideally these would be implemented in C.
--
CREATE OR REPLACE FUNCTION regexp_matches( citext, citext ) RETURNS TEXT[] AS $$
    SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION regexp_matches( citext, citext, text ) RETURNS TEXT[] AS $$
    SELECT pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION regexp_replace( citext, citext, text ) returns TEXT AS $$
    SELECT pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, 'i');
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION regexp_replace( citext, citext, text, text ) returns TEXT AS $$
    SELECT pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, CASE WHEN pg_catalog.strpos($4, 'c') = 0 THEN  $4 || 'i' ELSE $4 END);
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION regexp_split_to_array( citext, citext ) RETURNS TEXT[] AS $$
    SELECT pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION regexp_split_to_array( citext, citext, text ) RETURNS TEXT[] AS $$
    SELECT pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION regexp_split_to_table( citext, citext ) RETURNS SETOF TEXT AS $$
    SELECT pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION regexp_split_to_table( citext, citext, text ) RETURNS SETOF TEXT AS $$
    SELECT pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION strpos( citext, citext ) RETURNS INT AS $$
    SELECT pg_catalog.strpos( pg_catalog.lower( $1::pg_catalog.text ), pg_catalog.lower( $2::pg_catalog.text ) );
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION replace( citext, citext, citext ) RETURNS TEXT AS $$
    SELECT pg_catalog.regexp_replace( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), $3::pg_catalog.text, 'gi' );
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION split_part( citext, citext, int ) RETURNS TEXT AS $$
    SELECT (pg_catalog.regexp_split_to_array( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), 'i'))[$3];
$$ LANGUAGE SQL IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION translate( citext, citext, text ) RETURNS TEXT AS $$
    SELECT pg_catalog.translate( pg_catalog.translate( $1::pg_catalog.text, pg_catalog.lower($2::pg_catalog.text), $3), pg_catalog.upper($2::pg_catalog.text), $3);
$$ LANGUAGE SQL IMMUTABLE STRICT;

Antwoord 5, autoriteit 4%

U kunt ook een index maken op lower(email).


Antwoord 6

Vanaf PostgreSQL v12 kunt u een hoofdletterongevoelige ICU-sortering maken (als PostgreSQL is gebouwd met ICU-ondersteuning):

CREATE COLLATION english_ci (
   PROVIDER = 'icu',
   LOCALE = 'en-US@colStrength=secondary',
   DETERMINISTIC = FALSE
);

U kunt dat gebruiken in kolomdefinities:

ALTER TABLE users ALTER email TYPE text COLLATE english_ci;

Of je kunt het gebruiken in vergelijkingen of ORDER BY-clausules:

WHERE email COLLATE english_ci IN ('[email protected]', '[email protected]')

Other episodes