[sql] 중복 항목을 삭제하는 방법은 무엇입니까?

새 테이블을 생성하는 대신 자른 후 동일한 테이블에 고유 행을 다시 삽입 할 수도 있습니다. 한 번의 트랜잭션으로 모든 작업 을 수행하십시오 . 선택적으로를 사용하여 트랜잭션이 끝날 때 임시 테이블을 자동으로 삭제할 수 있습니다 ON COMMIT DROP. 아래를 참조하십시오.

이 접근 방식은 테이블 전체에서 삭제할 행이 많은 경우에만 유용합니다. 몇 개의 중복에 대해서는 일반 DELETE.

수백만 개의 행을 언급하셨습니다. 작업을 빠르게 하려면 세션에 충분한 임시 버퍼 를 할당 해야합니다. 현재 세션에서 임시 버퍼를 사용 하기 전에 설정을 조정 해야 합니다. 테이블의 크기를 확인하십시오.

SELECT pg_size_pretty(pg_relation_size('tbl'));

temp_buffers그에 따라 설정하십시오 . 메모리 내 표현에는 약간 더 많은 RAM이 필요하므로 충분히 반올림하십시오.

SET temp_buffers = 200MB;    -- example value

BEGIN;

-- CREATE TEMPORARY TABLE t_tmp ON COMMIT DROP AS -- drop temp table at commit
CREATE TEMPORARY TABLE t_tmp AS  -- retain temp table after commit
SELECT DISTINCT * FROM tbl;  -- DISTINCT folds duplicates

TRUNCATE tbl;

INSERT INTO tbl
SELECT * FROM t_tmp;
-- ORDER BY id; -- optionally "cluster" data while being at it.

COMMIT;

이 방법은 종속 개체가있는 경우 새 테이블을 만드는 것보다 우수 할 수 있습니다 . 테이블을 참조하는 뷰, 인덱스, 외래 키 또는 기타 개체. TRUNCATE어쨌든 깨끗한 슬레이트 (백그라운드의 새 파일)로 시작하고 큰 테이블 보다 훨씬 빠릅니다 DELETE FROM tbl( DELETE실제로 작은 테이블에서 더 빠를 수 있음).

큰 테이블의 경우 인덱스와 외래 키를 삭제하고 테이블을 다시 채우고 이러한 개체를 다시 만드는 것이 정기적으로 더 빠릅니다 . fk 제약 조건에 관한 한 새 데이터가 물론 유효한지 확인해야합니다. 그렇지 않으면 fk를 만들려고 할 때 예외가 발생합니다.

주 TRUNCATE보다 더 공격적으로 잠금이 필요합니다 DELETE. 동시로드가 많은 테이블의 경우 문제가 될 수 있습니다.

경우 TRUNCATE일반적 대한 옵션 아닌지 매체 작은 테이블 과 유사한 기술이있는 데이터 수정 CTE (포스트 그레스 9.1 +)를 :

WITH del AS (DELETE FROM tbl RETURNING *)
INSERT INTO tbl
SELECT DISTINCT * FROM del;
-- ORDER BY id; -- optionally "cluster" data while being at it.

큰 테이블의 경우 TRUNCATE더 느리기 때문에 더 빠릅니다. 그러나 작은 테이블의 경우 더 빠르고 간단 할 수 있습니다.

종속 개체가 전혀없는 경우 새 테이블을 만들고 이전 테이블을 삭제할 수 있지만이 보편적 인 접근 방식을 통해 얻을 수있는 것은 거의 없습니다.

사용 가능한 RAM에 맞지 않는 매우 큰 테이블의 경우 새 테이블을 만드는 것이 훨씬 빠릅니다. 개체에 따라 발생할 수있는 문제 / 오버 헤드와 비교하여이 값을 측정해야합니다.

답변

DELETE FROM table
 WHERE ctid NOT IN
  (SELECT MAX(s.ctid)
    FROM table s
    GROUP BY s.column_has_be_distinct);

DELETE FROM tablename
WHERE id IN (SELECT id
              FROM (SELECT id,
                             row_number() over (partition BY column1, column2, column3 ORDER BY id) AS rnum
                     FROM tablename) t
              WHERE t.rnum > 1);

create table test ( a text, b text );

insert into test values ( 'x', 'y');
insert into test values ( 'x', 'x');
insert into test values ( 'y', 'y' );
insert into test values ( 'y', 'x' );

insert into test values ( 'x', 'y');
insert into test values ( 'x', 'x');
insert into test values ( 'y', 'y' );
insert into test values ( 'y', 'x' );

insert into test values ( 'x', 'y');

select oid, a, b from test;

select o.oid, o.a, o.b from test o
    where exists ( select 'x'
                   from test i
                   where     i.a = o.a
                         and i.b = o.b
                         and i.oid < o.oid
                 );

delete from test
    where exists ( select 'x'
                   from test i
                   where     i.a = test.a
                         and i.b = test.b
                         and i.oid < test.oid
             );

DELETE FROM table_name
WHERE ctid NOT IN (
  SELECT max(ctid) FROM table_name
  GROUP BY column1, [column 2, ...]
);