[postgresql] PostgreSQL : 대소 문자를 구분하지 않는 쿼리를 만드는 방법

가장 일반적인 방법은 검색 문자열과 데이터를 소문자 또는 대문자로 만드는 것입니다. 그러나 두 가지 문제가 있습니다.

1. 영어로만 작동하지만 모든 언어로 작동하는 것은 아닙니다. (대부분의 언어로는 지원되지 않을 수도 있습니다.) 모든 소문자에 해당하는 대문자가있는 것은 아닙니다. 모든 대문자에 해당하는 소문자가있는 것은 아닙니다.
2. lower () 및 upper ()와 같은 함수를 사용하면 순차적 스캔이 제공됩니다. 인덱스를 사용할 수 없습니다. 테스트 시스템에서 lower ()를 사용하면 인덱스를 사용할 수있는 쿼리보다 약 2000 배 더 오래 걸립니다. (테스트 데이터의 행 수는 10 만 개가 조금 넘습니다.)

더 효과적 일 수있는 자주 사용되지 않는 솔루션이 3 개 이상 있습니다.

1. 대소 문자를 구분하지 않는 데이터 유형의 동작을 모방 한 citext 모듈을 사용하십시오 . 해당 모듈을로드 한 후을 사용하여 대소 문자를 구분하지 않는 색인을 작성할 수 있습니다 CREATE INDEX ON groups (name::citext);. (그러나 아래를 참조하십시오.)
2. 대소 문자를 구분하지 않는 데이터 정렬을 사용하십시오. 데이터베이스를 초기화 할 때 설정됩니다. 대소 문자를 구분하지 않는 데이터 정렬을 사용하면 클라이언트 코드에서 거의 모든 형식을 받아 들일 수 있으며 여전히 유용한 결과를 반환합니다. 또한 대소 문자를 구분하는 쿼리를 수행 할 수 없다는 의미이기도합니다.
3. 기능 색인을 작성하십시오. 을 사용하여 소문자 색인을 만듭니다 CREATE
INDEX ON groups (LOWER(name));. 쿼리가 좋아하는 당신은 인덱스를 활용할 수 있다는 것을 수행하는 데 SELECT id FROM groups WHERE LOWER(name) = LOWER('ADMINISTRATOR');, 또는 SELECT id FROM groups WHERE LOWER(name) = 'administrator';당신은해야 기억 하지만, LOWER ()를 사용 할 수 있습니다.

citext 모듈은 대소 문자를 구분하지 않는 데이터 형식을 제공하지 않습니다. 대신 각 문자열이 소문자처럼 작동합니다. 즉, lower()위의 3 번과 같이 각 문자열에서 호출 한 것처럼 작동합니다. 프로그래머가 문자열을 소문자로 기억할 필요가 없다는 장점이 있습니다. 그러나 citext를 사용하기 전에 문서에서 “문자열 비교 동작”및 “제한 사항”섹션을 읽어야합니다.

답변

SELECT id FROM groups where name ILIKE 'administrator'

SELECT id FROM groups where name ~* 'administrator'

SELECT id FROM groups WHERE name ~* 'adm'