Deleting duplicates rows from redshift

Question

I am trying to delete some duplicate data in my redshift table.

Below is my query:-

With duplicates
As
(Select *, ROW_NUMBER() Over (PARTITION by record_indicator Order by record_indicator) as Duplicate From table_name)
delete from duplicates
Where Duplicate > 1 ;

This query is giving me an error.

Amazon Invalid operation: syntax error at or near "delete";

Not sure what the issue is as the syntax for with clause seems to be correct. Has anybody faced this situation before?

Answer 1

Redshift being what it is (no enforced uniqueness for any column), Ziggy's 3rd option is probably best. Once we decide to go the temp table route it is more efficient to swap things out whole. Deletes and inserts are expensive in Redshift.

begin;
create table table_name_new as select distinct * from table_name;
alter table table_name rename to table_name_old;
alter table table_name_new rename to table_name;
drop table table_name_old;
commit;

If space isn't an issue you can keep the old table around for a while and use the other methods described here to validate that the row count in the original accounting for duplicates matches the row count in the new.

If you're doing constant loads to such a table you'll want to pause that process while this is going on.

If the number of duplicates is a small percentage of a large table, you might want to try copying distinct records of the duplicates to a temp table, then delete all records from the original that join with the temp. Then append the temp table back to the original. Make sure you vacuum the original table after (which you should be doing for large tables on a schedule anyway).

Answer 2

If you're dealing with a lot of data it's not always possible or smart to recreate the whole table. It may be easier to locate, delete those rows:

-- First identify all the rows that are duplicate
CREATE TEMP TABLE duplicate_saleids AS
SELECT saleid
FROM sales
WHERE saledateid BETWEEN 2224 AND 2231
GROUP BY saleid
HAVING COUNT(*) > 1;

-- Extract one copy of all the duplicate rows
CREATE TEMP TABLE new_sales(LIKE sales);

INSERT INTO new_sales
SELECT DISTINCT *
FROM sales
WHERE saledateid BETWEEN 2224 AND 2231
AND saleid IN(
     SELECT saleid
     FROM duplicate_saleids
);

-- Remove all rows that were duplicated (all copies).
DELETE FROM sales
WHERE saledateid BETWEEN 2224 AND 2231
AND saleid IN(
     SELECT saleid
     FROM duplicate_saleids
);

-- Insert back in the single copies
INSERT INTO sales
SELECT *
FROM new_sales;

-- Cleanup
DROP TABLE duplicate_saleids;
DROP TABLE new_sales;

COMMIT;

Full article: https://elliot.land/post/removing-duplicate-data-in-redshift

Answer 3

That should have worked. Alternative you can do:

With 
  duplicates As (
    Select *, ROW_NUMBER() Over (PARTITION by record_indicator
                                 Order by record_indicator) as Duplicate
    From table_name)
delete from table_name
where id in (select id from duplicates Where Duplicate > 1);

or

delete from table_name
where id in (
  select id
  from (
    Select id, ROW_NUMBER() Over (PARTITION by record_indicator
                                 Order by record_indicator) as Duplicate
    From table_name) x
  Where Duplicate > 1);

If you have no primary key, you can do the following:

BEGIN;
CREATE TEMP TABLE mydups ON COMMIT DROP AS
  SELECT DISTINCT ON (record_indicator) *
  FROM table_name
  ORDER BY record_indicator --, other_optional_priority_field DESC
;

DELETE FROM table_name
WHERE record_indicator IN (
  SELECT record_indicator FROM mydups);

INSERT INTO table_name SELECT * FROM mydups;
COMMIT;

Answer 4

Simple answer to this question:

1. Firstly create a temporary table from the main table where value of row_number=1.
2. Secondly delete all the rows from the main table on which we had duplicates.
3. Then insert the values of temporary table into the main table.

Queries:

1. Temporary table

   select id,date into #temp_a from (select *
from (select a.*, row_number() over(partition by id order by etl_createdon desc) as rn from table a where a.id between 59 and 75 and a.date = '2018-05-24') where rn =1)a

2. deleting all the rows from the main table.

   delete from table a where a.id between 59 and 75 and a.date = '2018-05-24'

3. inserting all values from temp table to main table

   insert into table a select * from #temp_a.

Answer 5

The following deletes all records in 'tablename' that have a duplicate, it will not deduplicate the table:

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);

Postgres administrative snippets

Answer 6

Your query does not work because Redshift does not allow DELETE after the WITH clause. Only SELECT and UPDATE and a few others are allowed (see WITH clause)

Solution (in my situation):

I did have an id column on my table events that contained duplicate rows and uniquely identifies the record. This column id is the same as your record_indicator.

Unfortunately I was unable to create a temporary table because I ran into the following error using SELECT DISTINCT:

ERROR: Intermediate result row exceeds database block size

But this worked like a charm:

CREATE TABLE temp as (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rownumber 
    FROM events
);

resulting in the temp table:

id | rownumber | ...
----------------
1  | 1         | ...
1  | 2         | ...
2  | 1         | ...
2  | 2         | ...

Now the duplicates can be deleted by removing the rows having rownumber larger than 1:

DELETE FROM temp WHERE rownumber > 1

After that rename the tables and your done.

Answer 7

• This method will preserve permissions and the table definition of the original_table.
• The most upvoted answer does not preserve permissions on the table or the original definition of the table.
• In real world production environment this method is how you should be doing as this is safest and easiest way to execute in production environment.

1. Create Table with unique rows

CREATE TABLE unique_table as
(
   SELECT DISTINCT * FROM original_table
)
;

2. Backup the original_table

CREATE TABLE backup_table as
(
   SELECT * FROM original_table
)
; 

3. Truncate the original_table

TRUNCATE original_table

4. Insert records from unique_table into original_table

INSERT INTO original_table
(
SELECT * FROM unique_table
)
;