PostgreSQL의 : 대소 문자를 구분 문자열 비교

간단한 무시 - 대소 비교 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');

등 및 사업자 ILIKE (예를 들어, 'adamB@a.com'와 같은) 하나의 값에 대한 작업,하지만 설정합니다.

해결법

==============================

1.첫째, 무엇을 할 수 없습니다 : ILIKE를 사용하지 않는 ...

첫째, 무엇을 할 수 없습니다 : ILIKE를 사용하지 않는 ...

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

... 제대로 표현에 인덱스를 생성하지 않아도 당신이 인덱스를 사용 (예를 들어, 인덱스를 생성 ZZZ YYY (낮은 (필드))에) :

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;

==============================
2.
```
select * 
where email ilike 'me@example.com'
```
ILIKE는 좋아하지만 경우를 구분 유사합니다. ) (대체 이스케이프 문자의 사용을 위해
```
where email ilike replace(replace(replace($1, '~', '~~'), '%', '~%'), '_', '~_') escape '~'
```
또는 당신은 탈출 텍스트 함수를 만들 수 있습니다; 텍스트 사용의 배열
```
where email ilike any(array['adamB@a.com', 'eveA@b.com'])
```

==============================

3.이 질문에 답하기 이후 상황이 마지막 4 년 동안 변경 및 추천 "ILIKE를 사용하지 않는"은 더 이상 (적어도 같은 일반적인 방법으로) 사실이 아니다.

이 질문에 답하기 이후 상황이 마지막 4 년 동안 변경 및 추천 "ILIKE를 사용하지 않는"은 더 이상 (적어도 같은 일반적인 방법으로) 사실이 아니다.

사실, 데이터 분포에 따라 괘 지수 ILIKE가 더 빨리 다음 citext 수 있습니다.

고유 인덱스를 들어 마이클의 테스트 설정을 사용할 때 볼 수있는 큰 차이가 실제로있다 :

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;

ILIKE를 사용하는 실행 계획 :

explain (analyze)
select * 
from y 
where email ilike ANY (ARRAY['Some.Name420@foobar.com','Some.Name42@foobar.com']);

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 ('{Some.Name420@foobar.com,Some.Name42@foobar.com}'::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 ('{Some.Name420@foobar.com,Some.Name42@foobar.com}'::text[]))
Planning time: 0.952 ms
Execution time: 61.004 ms

그리고 citext를 사용 :

explain (analyze)
select * 
from x 
where email = ANY (ARRAY['Some.Name420@foobar.com','Some.Name42@foobar.com']);

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 ('{Some.Name420@foobar.com,Some.Name42@foobar.com}'::citext[]))
Planning time: 0.115 ms
Execution time: 0.254 ms

참고 ILIKE 쿼리는 다음 사실 뭔가 다른에 ILIKE가 와일드 카드를 존중하는 것처럼 citext의 = 쿼리입니다.

그러나 고유하지 않은 인덱스의 상황이 다를. 다음 설정이 같은 요청하는 최근의 질문을 기반으로합니다

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

우리가 이름 컬럼에 대한 각각의 고유 한 값에 대한 각 테이블의 백만 행과 1000 개 고유 한 값을 가지고 그래서 우리는 1000 개 중복이있다. 3 개 다른 값을 찾고 쿼리 따라서 3000 개 행을 반환합니다.

이 경우 트라이 그램 인덱스는 BTREE 인덱스는 실질적으로 더 빠른 다음이다 :

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

그리고 citext 컬럼에 BTREE 지수는 지금 서열 번호 검색을 사용합니다

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

또한 GIN 인덱스의 크기는 citext 열에 하나는 실제로 작 :

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

위의 1.5 random_page_cost 세트 윈도우 노트북에 포스트 그레스 9.6.1을 사용하여 수행되었다

==============================

4.대소 문자를 구분 텍스트 데이터 유형을 사용합니다. 사용 citext :

대소 문자를 구분 텍스트 데이터 유형을 사용합니다. 사용 citext :

create table emails
(
user_id int references users(user_id)
email citext
);

insert into emails(user_id, email) values(1, 'linus.Torvalds@linUX.com');
insert into emails(user_id, email) values(2, 'iSteve.jobs@apple.com');

select * from emails where email in ('linus.torvalds@Linux.com','isteve.jobs@Apple.com');

경우에 당신은 당신의 contrib 디렉토리에 citext.sql을 찾을 수 없습니다, 복사하여 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;

==============================
5.또한 낮은 (이메일)에 인덱스를 만들 수 있습니다.

또한 낮은 (이메일)에 인덱스를 만들 수 있습니다.

==============================

6.

Use ‘Collate SQL_Latin1_General_CP1_CS_AS’ for it.
declare @a nvarchar(5)='a'
declare @b nvarchar(5)='A'

if(@a=@b Collate SQL_Latin1_General_CP1_CS_AS)
begin
print 'Match'
end
else
begin
print 'Not Matched'
end

from https://stackoverflow.com/questions/4482206/postgresql-case-insensitive-string-comparison by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 패키지 데이터베이스 / SQL과 I 배치 SQL 문 (0)	2020.06.24
[SQL] DB2 쿼리는 주어진 스키마에 대한 모든 테이블 이름을 검색하기 (0)	2020.06.24
[SQL] 나는 4.3 코드 첫 번째 엔티티 프레임 워크의 기본 스키마 이름을 변경할 수 있습니까? (0)	2020.06.24
[SQL] SQL Server 2008의 GUI에 고유 제한 조건을 추가 하시겠습니까? (0)	2020.06.24
[SQL] 어떻게 JSON에 MySQL 데이터베이스를 내보내려면? (0)	2020.06.24

복붙노트

[SQL] PostgreSQL의 : 대소 문자를 구분 문자열 비교

PostgreSQL의 : 대소 문자를 구분 문자열 비교

해결법

1.첫째, 무엇을 할 수 없습니다 : ILIKE를 사용하지 않는 ...

2.

3.이 질문에 답하기 이후 상황이 마지막 4 년 동안 변경 및 추천 "ILIKE를 사용하지 않는"은 더 이상 (적어도 같은 일반적인 방법으로) 사실이 아니다.

4.대소 문자를 구분 텍스트 데이터 유형을 사용합니다. 사용 citext :

5.또한 낮은 (이메일)에 인덱스를 만들 수 있습니다.

6.

'SQL' 카테고리의 다른 글

티스토리툴바