중복데이터 삭제 방법과 ctid (PostgreSQL)

테이블에 동일한 데이터가 중복으로 발생한 것을 발견했다.

여기서 중복이라는 것은 모든 컬럼 데이터가 동일한 row를 의미한다.

애초에 pk도 지정이 되어있지 않은 기존 설계도 문제이지만, 어찌되었든 이로인해 배치가 돌지 않는 문제가 발생하고 있으므로 중복데이터를 전부 삭제해야 했다.

구글링을 통해 1차로 알아본 방법은 아래 쿼리를 사용하는 것이다.

DELETE A
FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY eng_name ORDER BY eng_name) AS num, *
        FROM name
      ) A
WHERE num >= 2

헌데 이 방법은 문법오류 때문에 통하지 않았다.

블로그를 다시보니 MS-SQL 기준으로 작성된 듯한데, 아마 PostgreSQL에서는 다른 방법을 사용해야 한다는 판단이 들었다.

SELECT ROW_NUMBER() OVER (PARTITION BY eng_name ORDER BY eng_name) AS num, *
FROM name

일단 이 부분은 PostgreSQL에서도 원하는 결과를 얻을 수 있었다.

내가 실제로 중복을 제거할 테이블에서는 컬럼이 총 3개여서 3개 컬럼을 모두 PARTITION 을 나눌 기준으로 사용했지만 보안때문에 원 블로그의 예제문을 갖고 왔다.

간단히 말하면 중복된 row끼리 순번을 매기는 것이고, 1번을 제외한 모든 row는 제거하면 중복이 제거될 것이다.

SELECT rowid, ROW_NUMBER() OVER (PARTITION BY eng_name ORDER BY eng_name) AS num, *
FROM name

지금까지 Oracle을 주로 사용했기에, 위의 쿼리처럼 NUM이 1이 아닌 rowid를 뽑아서 대상건을 삭제하려고 했건만..

PostgreSQL에서는 rowid를 사용하지 않는다는걸 알게 되었다.

대신 ctid 라는 것을 사용한다.

SELECT ctid, ROW_NUMBER() OVER (PARTITION BY eng_name ORDER BY eng_name) AS num, *
FROM name

ctid 는 테이블 내부 행 버전의 물리적 위치를 나타낸다.

오라클의 rowid 와는 차이가 있는데 바로 지속성 이다.

rowid 는 해당 row가 삭제되고 다시 insert 되지 않는 이상 값이 변하지 않는다.

update 시에도 값이 변하지 않는다.

table move 명령 등을 이용한 테이블 변경시에는 값이 변한다.

반면, ctid 는 update 시에도 값이 변합니다.

따라서 중복 행 삭제를 위해 아래와 같이 사용합니다.

DELETE FROM name
WHERE ctid IN (
    SELECT A.ctid
    FROM (
        SELECT ctid,
                     ROW_NUMBER() over (PARTITION BY eng_name ORDER BY eng_name) AS num
        FROM name
    ) A
    WHERE A.num > 1
)

[SQL Server / MS-SQL] PK 없는 테이블 중복 데이터 제거

PostgresDBA.com - 포스트그레스큐엘,PostgreSQL,Postgres Plus Advanced Server