How to deduplicate identical rows in a table

If the dups were in the same file, then they'll have the same epoch, so that's not useful here. Otherwise, you can use the epoch to identify different rows.

In this case, your option is probably the only way - move them to a table with a distinct property, and then replace the unique records back into the original table.

Agree with Curtis.

If you need to keep GRANTs, VIEWs, and other stuff, you could go:

SELECT COPY_TABLE('schema.table','schema,duptable');
TRUNCATE TABLE schema.table;
INSERT /*+DIRECT */ INTO schema.table 
SELECT DISTINCT * FROM schema.duptable;

and, if all is well:

DROP TABLE schema.duptable;

Otherwise, just to keep the table available for querying as long as possible, go:

CREATE TABLE schema.deduped AS /*+DIRECT */ 
SELECT DISTINCT * FROM schema.table;
ALTER TABLE schema.table RENAME TO schema.old;
ALTER TABLE schema.deduped RENAME TO schema.table;

and, if all is well:

DROP TABLE schema.old;

good luck ...

Here's another way without having to do any table renaming:

dbadmin=> SELECT * FROM dups;
 c1 | c2 | c3
----+----+----
  1 | A  | B
  1 | A  | B
  2 | C  | D
  2 | C  | D
  3 | E  | F
(5 rows)

dbadmin=> CREATE TEMP TABLE dups_temp ON COMMIT PRESERVE ROWS AS /*+ DIRECT */ SELECT DISTINCT * FROM dups;
CREATE TABLE

dbadmin=> DELETE /*+ DIRECT */ FROM dups;
 OUTPUT
--------
      5
(1 row)

dbadmin=> INSERT /*+ DIRECT */ INTO dups SELECT * FROM dups_temp;
 OUTPUT
--------
      3
(1 row)

dbadmin=> SELECT purge_table('dups');
                                 purge_table
-----------------------------------------------------------------------------
 Task: purge operation
(Table: public.dups) (Projection: public.dups_super)

(1 row)

dbadmin=> DROP TABLE dups_temp;
DROP TABLE

dbadmin=> SELECT * FROM dups;
 c1 | c2 | c3
----+----+----
  1 | A  | B
  2 | C  | D
  3 | E  | F
(3 rows)

Just for fun, here is yet another way to de-dup a table!

dbadmin=> SELECT * FROM dups ORDER BY 1, 2, 3;
 c1 | c2 | c3
----+----+----
  1 | A  | B
  1 | A  | B
  2 | C  | D
  2 | C  | D
  3 | E  | F
  4 | A  | A
  4 | A  | A
  4 | A  | A
  4 | A  | A
(9 rows)

dbadmin=> ALTER TABLE dups ADD COLUMN delete_min INT;
ALTER TABLE

dbadmin=> UPDATE /*+ DIRECT */ dups SET delete_min = RANDOMINT(1000000000000000000);
 OUTPUT
--------
      9
(1 row)

dbadmin=> SELECT * FROM dups ORDER BY 1, 2, 3;
 c1 | c2 | c3 |     delete_min
----+----+----+--------------------
  1 | A  | B  |  58716713060586072
  1 | A  | B  | 559370968987428959
  2 | C  | D  | 829850418262285163
  2 | C  | D  | 936790182385777294
  3 | E  | F  | 432454667506309661
  4 | A  | A  | 593514762668769131
  4 | A  | A  | 281271428572528272
  4 | A  | A  | 154576816355792579
  4 | A  | A  |  10171244835362256
(9 rows)

dbadmin=> SELECT * FROM dups GROUP BY c1, c2, c3, delete_min HAVING COUNT(*) > 1; -- Verify each record for group got a unique value for delete_min
 c1 | c2 | c3 | delete_min
----+----+----+------------
(0 rows)

dbadmin=> DELETE FROM dups WHERE dups.delete_min > (SELECT MIN(dups2.delete_min) FROM dups dups2 WHERE dups2.c1 = dups.c1 AND dups2.c2 = dups.c2 AND dups2.c3 = dups);
 OUTPUT
--------
      5
(1 row)

dbadmin=> SELECT * FROM dups ORDER BY 1, 2, 3;
 c1 | c2 | c3 |     delete_min
----+----+----+--------------------
  1 | A  | B  |  58716713060586072
  2 | C  | D  | 829850418262285163
  3 | E  | F  | 432454667506309661
  4 | A  | A  |  10171244835362256
(4 rows)

dbadmin=> ALTER TABLE dups DROP COLUMN delete_min;
ALTER TABLE

dbadmin=> SELECT purge_table('dups');
                                 purge_table
-----------------------------------------------------------------------------
 Task: purge operation
(Table: public.dups) (Projection: public.dups_super)

(1 row)

dbadmin=> SELECT * FROM dups ORDER BY 1, 2, 3;
 c1 | c2 | c3
----+----+----
  1 | A  | B
  2 | C  | D
  3 | E  | F
  4 | A  | A
(4 rows)

I cannot test it on my smartphone (still on vacation...) but the following - two steps - approach intrigues me:

Step 1:
CREATE LOCAL TEMPORARY TABLE xyz ON COMMIT PRESERVE ROWS AS /* +direct */ SELECT *, HASH(c1, c2,...) AS hs FROM my table KSAFE 0;

Step 2:
CREATE TABLE nodups AS /* +direct */ SELECT c1, c2,... FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY hs ORDER BY hs) AS rn) FROM xyz) x WHERE rn=1;

Hope I didn't enter too many typos using this tiny keyboard...

Get Outlook for Android

________________________________
From: marcothesane
Sent: Thursday, January 3, 2019 6:42:44 PM
To: Maurizio Felici
Subject: Re: [AskVertica] How to deduplicate identical rows in a table

Vertica Forum https://forum.vertica.com/
New Comment on How to deduplicate identical rows in a table in AskVertica

Agree with Curtis.

If you need to keep GRANTs, VIEWs, and other stuff, you could go:

SELECT COPY_TABLE('schema.table','schema,duptable'); TRUNCATE TABLE schema.table; INSERT /*+DIRECT */ INTO schema.table SELECT DISTINCT * FROM schema.duptable;

and, if all is well:

DROP TABLE schema.duptable;

Otherwise, just to keep the table available for querying as long as possible, go:

CREATE TABLE schema.deduped AS /*+DIRECT */ SELECT DISTINCT * FROM schema.table; ALTER TABLE schema.table RENAME TO schema.old; ALTER TABLE schema.deduped RENAME TO schema.table;

and, if all is well:

DROP TABLE schema.old;

good luck ...

Reply to this email directly or follow the link below to check it out: https://forum.vertica.com/discussion/comment/241962#Comment_241962