PostgreSQL에서 대소문자를 구분하지 않는 쿼리를 작성하는 방법

문자의 대소문자가 같지 않아도 텍스트를 찾으려면 패턴 검색에는 ILIKE를, 비교에는 LOWER 또는 UPPER로 정규화한 값을 사용하세요. 열에서 대소문자를 구분하지 않는 비교가 일반적인 동작이어야 한다면 CITEXT 열을 사용할 수도 있습니다. 적절한 방법은 패턴 검색이 필요한지, 열을 어떻게 조회하는지, 어떤 인덱스와 로캘 설정을 사용하는지에 따라 달라집니다.

아래 예제에서는 id, age, type 열이 있는 animal 테이블을 사용하며, type 값이 Kitten인 행도 포함합니다. SQL은 동작을 설명하기 위한 예시이며 PostgreSQL 서버에서 실행하지 않았습니다.

대소문자를 구분하지 않는 패턴 검색에 ILIKE 사용

대소문자를 구분하지 않는 동등 비교에는 type ILIKE 'kitten'을 사용합니다. 값 안에 패턴이 포함되는지 검색하려면 type ILIKE '%kitten%'처럼 와일드카드를 사용하세요. %는 임의의 문자 시퀀스와 일치하고 _는 문자 하나와 일치합니다. ILIKE는 PostgreSQL에서 대소문자를 구분하지 않는 LIKE에 해당하며, 동작은 현재 활성 로캘을 따릅니다.

일반적인 사용 방식에서 LIKE는 대소문자를 구분하므로, 대소문자를 구분하는 콜레이션에서는 type LIKE 'kitten'이 Kitten과 일치하지 않습니다. SIMILAR TO는 SQL 정규식과 비슷한 패턴 문법을 사용하고, ILIKE는 더 단순한 LIKE 와일드카드 문법을 사용합니다. 두 연산자는 서로 다른 패턴 요구에 맞으므로 모든 쿼리에서 서로 바꿔 쓸 수 있는 것은 아닙니다. PostgreSQL의 ~~* 연산자는 ILIKE의 다른 표기입니다.

동등 비교와 비슷한 검색이 필요하면 정확한 패턴을 사용하세요. 더 넓은 패턴과 일치시켜야 할 때만 % 또는 _를 추가합니다.

LOWER 또는 UPPER로 비교 양쪽을 정규화하기

대소문자를 구분하지 않는 동등 비교를 하려면 열과 검색 값에 같은 대소문자 변환을 적용합니다. 예를 들면 LOWER(type) = LOWER('kitten')과 같습니다. 양쪽 모두에 UPPER를 적용해도 됩니다. LOWER는 데이터베이스 로캘에 따라 텍스트를 변환하는 함수입니다. INITCAP은 단어의 첫 글자를 대문자로 바꾸므로 대소문자를 구분하지 않는 비교를 일반적으로 대신할 수 없습니다.

언어별 문자를 다룰 때는 대소문자 변환과 비교 결과가 로캘 및 콜레이션에 따라 달라질 수 있습니다. 텍스트 변환이 영어에만 적용된다거나 모든 로캘에서 문자를 똑같이 처리한다고 가정하지 마세요. 데이터와 비교 규칙에 맞는 데이터베이스 로캘과 콜레이션을 선택하고 테스트하세요.

대소문자를 구분하지 않는 검색을 위한 인덱스 추가

type에 만든 일반 인덱스는 LOWER(type) 조건에 같은 인덱스 표현식처럼 사용되지 않을 수 있습니다. 정규화된 동등 비교 쿼리에는 쿼리와 동일한 표현식을 사용하는 표현식 인덱스를 만드세요. 예를 들면 CREATE INDEX animal_type_lower_idx ON animal (LOWER(type));입니다. 이에 대응하는 UPPER(type) 인덱스는 UPPER(type)를 사용하는 쿼리를 지원할 수 있습니다.

인덱스가 유용하려면 해당 인덱스의 표현식과 연산자가 쿼리를 지원해야 하고, 플래너가 실제로 그 인덱스를 선택해야 합니다. 인덱스가 쿼리 속도 향상을 보장하지는 않습니다. PostgreSQL의 pg_trgm 확장 기능은 지원되는 LIKE 및 ILIKE 검색에 GIN 또는 GiST 삼중 문자 인덱스를 제공할 수 있습니다. 효과는 패턴, 테이블, 작업 부하에 따라 달라집니다. 인덱스를 선택하기 전에 실제 데이터를 대표하는 조건으로 실행 계획과 소요 시간을 확인하세요.

대소문자만 다른 중복 값을 거부해야 한다면 CREATE UNIQUE INDEX animal_type_lower_unique ON animal (LOWER(type)); 같은 고유 표현식 인덱스로 해당 표현식이 적용되는 값에 규칙을 적용할 수 있습니다. 이는 스키마 설계 결정이므로 적용 전에 의도한 데이터 규칙에 맞는지 확인하세요.

대소문자 구분 없는 열에 CITEXT 사용

CITEXT 확장 기능은 비교에 대소문자를 구분하지 않는 텍스트 형식을 제공하므로, 일반 동등 연산으로도 대소문자가 다른 값을 일치시킬 수 있습니다. CREATE EXTENSION citext;로 확장 기능을 활성화한 다음, 예를 들어 type CITEXT처럼 열을 CITEXT로 선언하세요. 그러면 SELECT id, age, type FROM animal WHERE type = 'kitten'; 같은 쿼리에서 해당 형식의 대소문자를 구분하지 않는 비교 동작에 따라 저장된 Kitten 값을 찾을 수 있습니다.

열에서 대소문자를 구분하지 않는 비교가 기본 동작이어야 할 때 CITEXT를 사용하면 편리합니다. 그렇다고 다른 방법보다 항상 빠르거나 비용이 적게 드는 것은 아니며, 인덱스와 콜레이션, 작업 부하를 고려할 필요도 그대로 남습니다. 데이터베이스에서 확장 기능을 사용할 수 있어야 하며 활성화도 해야 합니다. 도입하기 전에 로캘과 데이터에 대한 문서화된 비교 동작을 검토하세요.

정규화 개념을 간단히 보여 주는 Python 예제는 ASCII 표기가 다른 두 문자열을 비교합니다. 이 예제는 Python 문자열 동작만 보여 줍니다. PostgreSQL SQL을 실행하거나 데이터베이스 콜레이션 동작을 입증하지는 않습니다.

stored_type = "Kitten"
search_type = "kitten"
print(stored_type.lower() == search_type.lower())

True