SQL Server와 PostgreSQL에서 문자열 비교 차이점

 

DB 마이그레이션 작업 중에 생각하지 못한 부분을 발견했다.

마이그레이션 작업은 대부분 함수를 해당 DB 엔진에 맞게 수정하는 것이였는데, 쿼리가 아예 똑같고 데이터도 똑같은데 결과 가 달라서 보니 SQL Server와 PostgreSQL의 문자열 비교 동작 방식이 다르기 때문에 나타난 현상이었다.

그에 관련해 알아본 것을 포스팅한다.

 

---

 

데이터베이스 관리 시스템인 SQL Server와 PostgreSQL은 문자열 비교에 있어서 서로 다른 동작 방식을 가지고 있다. 이러한 차이점은 두 개 이상의 데이터베이스 관리 시스템을 사용하는 경우에는 예기치 않은 결과를 초래할 수 있기 때문에, 해당 사항을 인지하고 있는 것이 좋겠다.

 

SQL Server와 PostgreSQL의 공백 비교

아래 예제를 각 데이터베이스에서 조회해보자. 결과가 서로 다를 것이다.

SELECT
    CASE WHEN 'a' = 'a ' THEN 'math'
         ELSE 'no math'
    END;

 

 

SQL Server의 문자열 비교

SQL Server 문서를 보면 아래와 같이 표기되어있다.

The SQL Server Database Engine follows the ANSI/ISO SQL-92 specification (Section 8.2, Comparison Predicate, General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations. The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, the Database Engine doesn't pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this predicate doesn't violate the section of the ANSI SQL-92 specification mentioned earlier.

 

SQL Server에서는 기본적으로 문자열 비교 시 패딩이 적용된다. 이는 문자열의 길이가 다른 경우에도 비교할 때 자동으로 패딩이 채워짐을 의미한다. 

하지만 LIKE 구문의 경우 위에서 보이듯 해당 사항이 적용되지 않으니 유의한다.

 

PostgreSQL의 문자열 비교

PostgreSQL은 기본적으로 표준 문자열 비교 방식을 사용한다. 즉, 공백과 다른 문자들도 모두 의미가 있으며, 문자열을 완전히 일치하는지를 확인한다.

 

서로 다른 문자 인식

아래 예제를 각 데이터베이스에서 조회해보면 결과가 다른것을 알 수 있다.

SELECT
    CASE WHEN '"' = '＂' THEN 'math'
         ELSE 'no math'
    END;

 

PostgreSQL은 SQL Server와 달리 기본적으로 Collation 설정에 따라 문자열을 비교한다. 따라서 PostgreSQL에서는 '('와 '（'과 같은 서로 다른 유니코드 문자를 다른 문자로 취급한다. 해당 설정은 데이터베이스, 테이블, 열 단위로 지정할 수 있다. 

Collection 정렬 규칙을 간단히 보면 아래와 같다.

1. 대소문자 구분(Case Sensitivity): 대소문자를 구분하여 문자열을 비교하는지 여부를 결정. 대소문자를 구분하지 않는 경우, 대문자와 소문자를 동일하게 취급.
2. 폭(Width) 구분(Width Sensitivity): 문자의 폭을 구분하여 문자열을 비교하는지 여부를 결정. 폭 구분을 하지 않는 경우, 각 문자의 폭을 무시하고 비교하기 때문에, 'ａ'와 'a'를 같은 문자로 취급.
3. 발음 구분(Accent Sensitivity): 악센트 기호를 구분하여 문자열을 비교하는지 여부를 결정. 발음 구분을 하지 않는 경우, 악센트 기호를 무시하고 비교하기 때문에, 'cafe'와 'café'를 같은 문자열로 취급.

 

 

참고 : https://learn.microsoft.com/en-us/sql/t-sql/language-elements/string-comparison-assignment?view=sql-server-ver16 

= (String comparison or assignment) - SQL Server

https://www.postgresql.org/docs/current/collation.html

24.2. Collation Support