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

1. PostgreSQL에서 PATTERN MATCHING 사용
2. LOWER 기능을 사용하여 PostgreSQL에서 검색하는 동안 유사한 사례 만들기
3. PostgreSQL에서 CASE CONVERSION 사용
4. PostgreSQL에서 CASE-INSENSITIVE 일치를 수행하기 위해 테이블을 생성하는 동안 CITEXT 사용

DBMS에서 쿼리를 작성하는 동안 절이나 구문을 충족하는 데 필요한 조건에 따라 일부 문자가 대문자 또는 소문자여야 하는 경우가 종종 있습니다. 결과적으로 상황에 관계없이 쿼리를 완료하고 일반적인 구문 오류를 방지하는 방법을 찾아야 합니다.

이 기사에서는 PostgreSQL에서 대소문자를 구분하지 않는 쿼리를 작성하는 방법에 대해 설명합니다.

PostgreSQL에서 PATTERN MATCHING 사용

PostgreSQL에서 쿼리를 최적화하는 방법 중 하나는 데이터베이스에서 무언가를 찾고 포함된 개체와 일치하는 특정 문자열을 제공해야 하는 경우에 사용했던 것과 동일한 문자 대소문자를 사용해야 할 수 있습니다. 검색하는 동안 개체.

이전에 개체 이름을 정의했을 때 개체 이름의 정확한 구문을 잊어버린 경우 문제가 발생할 수 있습니다.

여기에서 PATTERN MATCHING과 같은 간단한 것을 사용할 수 있습니다. ID, AGE 및 TYPE이 포함된 ANIMAL 테이블이 있습니다.

테이블에는 이미 다음과 같은 데이터가 포함되어 있습니다.

       ID     AGE   TYPE
1     2 12   "Horse"
2     1 3    "Cat"
3     3 4    "Kitten"

아래 쿼리를 활용하여 KITTEN 유형의 모든 ANIMALS를 찾을 수 있습니다.

질문:

select * from ANIMAL where type = 'Kitten';

출력:

그러나 다음 쿼리를 작성하는 경우:

select * from ANIMAL where type = 'kitten';

그러면 결과에서 아무것도 얻지 못할 것입니다. 이것은 문자 'k'가 'K'와 같지 않기 때문에 발생합니다. 이를 해결하기 위해 ILIKE 연산자를 사용하여 대소문자를 구분하지 않도록 할 수 있습니다.

질문:

select * from ANIMAL where type ILIKE 'kitten';

이 쿼리는 KITTEN 유형의 모든 동물을 반환합니다. 이 코드에 대한 수정은 ILIKE와 대안적으로 사용할 수 있는 부호 연산자일 수 있습니다.

질문:

select * from ANIMAL where type ~~* 'kitten';

우리가 사용할 수 있는 또 다른 방법은 LIKE 또는 SIMILAR TO 연산자이지만 올바른 대소문자로 이름 부분을 기억하는 경우에만 사용할 수 있습니다.

예를 들어 KITTEN을 검색하려면 이름에 ITTEN이 작고 K가 SMALL 또는 BIG인 경우 다음과 같이 작성하여 결과를 쿼리할 수 있습니다.

질문:

select * from ANIMAL where type similar to '%itten';

이는 좋은 대안이 아니며 사용자가 개체를 만드는 동안 사용되는 명명 규칙에 대해 어느 정도 알고 있는 경우 사용해야 합니다. ILIKE는 제공된 문자열과 패턴을 일치시킬 때 더 나은 대소문자 구분 처리를 제공합니다.

LOWER 기능을 사용하여 PostgreSQL에서 검색하는 동안 유사한 사례 만들기

패턴을 문자열과 일치시키는 매우 효율적인 방법은 두 패턴의 문자를 유사하게 만드는 것입니다. 모든 문자를 LOWER 또는 UPPER로 변환한 다음 각각 일치시킵니다.

PostgreSQL은 확인을 위해 LOWER() 함수와 UPPER() 함수를 제공합니다.

질문:

select * from ANIMAL where lower(type) = lower('kitten')

또는

select * from ANIMAL where lower(type) = lower('kitten')

출력:

유사하게 INITCAP을 사용하여 패턴과 문자열의 시작 문자만 대문자로 표시하고 나머지는 일치 및 확인을 위해 소문자로 표시할 수 있습니다.

질문:

select * from ANIMAL where initcap(type) = initcap('kitten')

PostgreSQL에서 CASE CONVERSION 사용

PostgreSQL의 ‘INDEXES ON EXPRESSIONS’는 대형 테이블의 쿼리 결과를 가속화합니다. 시간이 더 걸리는 테이블에서 동일한 쿼리를 반복적으로 호출하는 대신 인덱스를 생성한 다음 쿼리할 때 사용할 수 있습니다.

CASE CONVERSIONS는 기존 INDEXES를 다시 업데이트해야 할 수 있으므로 유효하지 않게 렌더링할 수 있음을 기억하십시오. 이 CASE MATCHING에 대한 인덱스를 생성하기 위해 다음 쿼리를 작성할 수 있습니다.

질문:

create index lower_col on ANIMAL (lower(type));

또는

create index upper_col on ANIMAL (upper(type));

INITCAP도 마찬가지입니다. 이러한 INDEXES는 ROW INSERTION에 대한 제약 조건을 적용하는 데 사용할 수도 있습니다. 여기서 다른 CASE가 있는 데이터 세트가 INSERTED인 경우 중복 시 무효화될 수 있습니다.

LIKE 및 ILIKE 쿼리 속도를 높이기 위해 PG_TRGM_EXTENSION과 함께 GIN 또는 GIST 인덱스를 사용할 수 있습니다.

PostgreSQL에서 CASE-INSENSITIVE 일치를 수행하기 위해 테이블을 생성하는 동안 CITEXT 사용

PostgreSQL의 일반적인 일치에 대한 또 다른 대안은 CITEXT 절을 사용하는 것입니다. 문자열을 패턴과 일치시키려고 할 때마다 사용자가 LOWER()를 넣는 대신 값을 비교할 때 내부적으로 LOWER()를 호출합니다.

TYPE 열을 CITEXT로 사용하여 ANIMAL 테이블을 생성해 보겠습니다.

create extension CITEXT; --creating the extension first

create table ANIMAL(
    id INT PRIMARY KEY,
    age INT,
    TYPE CITEXT
)

그런 다음 이전 테이블에서 동일한 값을 INSERT합니다. 이제 다음 쿼리를 사용하여 결과를 반환합니다.

select * from ANIMAL where type = 'kitten';

칼럼에서 CITEXT를 사용하는 것이 위에 제시된 많은 솔루션보다 저렴하고 효율적이며 빠르다는 것을 알 수 있습니다. CITEXT는 데이터베이스의 LC_CTYPE 설정에 따라 다르며 필요에 따라 수정할 수 있습니다.

다음은 일치하기 전에 CASE CONVERSIONS에 대한 몇 가지 중요한 사항입니다.

1. 사례를 다른 언어(영어 제외)로 변환할 수 없습니다.
2. LOWER() 및 UPPER() 기능은 인덱싱이 없기 때문에 더 느립니다.

이것은 PostgreSQL에서 문자열과 패턴을 비교하는 다양한 방법입니다.