Postgresql 에 데이터를 업데이트하는 과정에서는 insert 를 할 때 on conflict 절에서 고유 키를 사용하여 충돌을 감지하고 충돌이 나는 경우에는 고유 키를 가진 컬럼의 값을 업데이트하고 그렇지 않은 경우 새로운 행을 추가해주게 된다.
INSERT INTO {table_name} ({table_columns})
SELECT {update_columns} FROM {update_table}
ON CONFLICT ({unique_key}) DO UPDATE
SET {update_columns};
위의 쿼리와 같이 ON CONFLICT 를 통해 고유키를 통해 업데이트의 여부를 결정하게 된다. 위의 쿼리를 통해 데이터를 업데이트 하려고 했는데 고유키 값은 분명 중복이 되지 않는 것을 확인했지만 업데이트를 진행할 때 동일한 행이 생성되는 문제가 있어서 무엇이 문제인지 열심히 찾아보았다.
문제의 원인은 고유키로 설정한 값에 Null 값이 포함되어있었던 것이 문제가 되었다.
예를 들어, a(not null), b(nullable), c(not null) 의 조합으로 된 고유 키를 가지고 있다고 한다면
PostgreSQL 에서는 null 의 값을 서로 다른 값으로 간주하기 때문에 테이블 안에 (a: 1, b: Null, c: 2) 의 행이 있다고 가정할 경우 동일한 고유키를 가진 값을 insert 한다고 했을 때 충돌을 감지하지 못한다.
결국에는 동일한 값이지만 테이블에는 (a: 1, b: Null, c: 2) 의 행이 2개가 생기게 된다.
따라서, 고유키의 값에 Null 값이 들어가있다면 충돌이 발생하지 않아 고유키가 동일해도 새로운 행이 계속해서 추가될 수 있다.
어떻게 보면 고유키로 설정한 컬럼은 Not Null 로 설정해야하는 것이 당연하지만 혹시나 테이블을 먼저 생성하는 것이 아닌 작성한 코드를 통해 테이블을 간접적으로 생성하게 되었을 때 Not Null 설정을 하기 어렵기 때문에 Null 값이 들어가 있다면 Null 값을 다른 값으로 대체해서 데이터를 입력해주어야 한다.
정리해보면 고유키를 통해 업데이트를 하려고 한다면 고유키가 Null 값을 가지지는 않는지 확인해서 Null 값이 들어가지 않도록 별도의 작업을 해주어야 한다는 점을 유의해야 한다.