[PostgreSQL] ctid로 중복 데이터 제거하기

🍒 PostgreSQL로 중복 데이터 제거하고 하나만 남기기

PK를 여러 개의 컬럼의 그룹으로 사용하고 있는 테이블에 데이터가 중복으로 적재되었다. 유일성이 엄격하게 지켜지지 않는 DB라 date_insert_dttm 컬럼을 제외하고 모든 컬럼의 값이 동일한 행이 2개씩 생긴 상태를 해결하기 위해 ctid를 사용해 DELETE를 시도했다.

DELETE FROM my_table t1 USING my_table t2
 WHERE t1.col1=t2.col1 
   and t1.col2=t2.col2 
   -- .. 모든 중복 컬럼(또는 PK 컬럼) 조건을 걸어줍니다.
   and t1.ctid<t2.ctid
;

 

🚫 ctid만으로는 삭제가 되지 않는 경우?

이 프로젝트에서는 지역별로 DB 접속정보가 달랐는데, A 지역에서는 위의 쿼리로 삭제가 되었지만 B 지역에서는 다음과 같은 에러가 발생하며 삭제가 되지 않았다.

SQL Error [XX000]: ERROR: DELETE uses system-defined column "my_table.ctid" without the necessary companion column "my_table.gp_segment_id"

 

✅ gp_segment_id를 조건절에 추가하여 중복 데이터 제거하기

에러 메시지에서 친절하게 알려준 것처럼 gp_segment_id를 조건절에 추가하니 정상적으로 삭제가 되었다. 분산 DB를 사용하는 경우 gp_segment_id를 함께 사용해야 데이터의 주소를 잘 찾아서 지울 수 있는 것 같다!

DELETE FROM my_table t1 USING my_table t2
 WHERE t1.col1=t2.col1 
   and t1.col2=t2.col2 
   -- .. 모든 중복 컬럼(또는 PK 컬럼) 조건을 걸어줍니다.
   and t1.ctid<t2.ctid
   and t1.gp_segment_id = t2.gp_segment_id
;

 

✅ 방법 2. ctid, rownum 조합으로 삭제하기

위의 방법으로 원하는 결과를 얻지 못할 경우 다음과 같이 처리할 수도 있다.

DELETE FROM my_table
WHERE (ctid, gp_segment_id) IN (
    SELECT A.ctid, A.gp_segment_id
    FROM (
        SELECT ctid,
        		gp_segment_id,
                     ROW_NUMBER() over (PARTITION BY [중복 컬럼 목록] ORDER BY {OPTION: 정렬 기준}) AS NUM
        FROM my_table
    ) A
    WHERE A.NUM > 1
)