[PostgreSQL] citext — 대소문자 무시 타입으로 이메일 UNIQUE를 건다

설치·접속: PostgreSQL 설치와 접속

부제: 회원가입에서 'Tom@X.com'과 'tom@x.com'을 같은 계정으로 막고 싶은데 lower(email) 함수 인덱스 도배가 지겨울 때

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE acct (id serial primary key, email citext UNIQUE);
INSERT INTO acct(email) VALUES ('Tom@X.com');
-- 대소문자만 다른 값으로 재가입 시도
INSERT INTO acct(email) VALUES ('tom@x.com');

ERROR:  duplicate key value violates unique constraint "acct_email_key"
DETAIL:  Key (email)=(tom@x.com) already exists.

컬럼 타입을 citext로 잡으면 비교가 항상 소문자 기준으로 일어난다. 그래서 UNIQUE 제약이 lower() 없이도 대소문자 무시로 동작해 tom@x.com 재가입이 DB단에서 막힌다. 조회도 마찬가지다 — 입력을 어떻게 대문자로 쳐도 저장된 원본 표기 그대로 찾아준다.

SELECT * FROM acct WHERE email = 'TOM@X.COM';

 id |   email
----+-----------
  1 | Tom@X.com
(1 row)

값 자체는 입력한 원래 대소문자(Tom@X.com)로 보존되고, 비교만 대소문자를 무시한다. 흔히 쓰는 WHERE lower(email)=lower(?) + CREATE INDEX ... (lower(email)) 조합을 타입 하나로 대체하는 셈이다. 매 쿼리마다 lower()를 빠뜨려 버그를 내거나, UNIQUE는 함수 인덱스로 따로 걸어야 하는 번거로움이 사라진다.

이렇게도 쓴다

citext 값끼리의 등호 비교는 대소문자를 무시한다.

SELECT 'Tom@x.com'::citext = 'tom@X.COM'::citext AS same;
-- same = t

 

기존 text 컬럼을 citext로 바꾼다. (조합: ALTER TABLE)

ALTER TABLE acct ALTER COLUMN email TYPE citext;

 

lower(email) 표현식 인덱스 패턴 — citext를 안 쓸 때의 전통적 대안. 조회마다 lower()를 반드시 붙여야 인덱스가 먹는다.

CREATE INDEX idx_acct_lower ON acct (lower(email::text));
SELECT * FROM acct WHERE lower(email::text) = lower('TOM@X.COM');

 

닉네임처럼 중복을 대소문자 무시로 막을 컬럼에도 그대로 쓴다.

ALTER TABLE acct ADD COLUMN nick citext UNIQUE;

 

신규 프로젝트라면 nondeterministic collation도 검토한다 — citext 확장 없이 코어 기능으로 대소문자 무시 컬럼을 만드는 대안. LIKE 등 일부 패턴 연산에 제약이 있으니 요구사항을 확인하고 고른다. (조합: CREATE COLLATION)

CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE TABLE acct2 (email text COLLATE ci UNIQUE);