Another Way to De-Duplicate Table Rows: Quick Tip

To remove duplicate table rows it is typically better (i.e. faster) to create a temporary table that contains all of the distinct rows from a…

OpenText profile picture
OpenText

February 8, 20192 minute read

Pfeile aus Beton, gestapelt, 3D Rendering, digital generiert, Business, Konzept, Idee, Symbol, Richtung, verschieden, farbig, unterschiedlich, Hinweis, verwirrend, uneinig, Meinungsverschiedenheit, Minimalismus, Einfachheit

To remove duplicate table rows it is typically better (i.e. faster) to create a temporary table that contains all of the distinct rows from a table, drop the original table, then rename the temp table to the original table’s name.

Example:

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

dbadmin=> CREATE TABLE dups_new LIKE dups INCLUDING PROJECTIONS;
CREATE TABLE

dbadmin=> INSERT /*+ DIRECT */ INTO dups_new SELECT DISTINCT * FROM dups;
OUTPUT
--------
4
(1 row)

dbadmin=> DROP TABLE dups;
DROP TABLE

dbadmin=> ALTER TABLE dups_new RENAME TO dups;
ALTER TABLE

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

The issue with that solution is that you’ll need to be sure that the original table grants are restored if they exist.

For smaller tables that have duplicate rows, here is another method to remove them that doesn’t involve creating a new table.

dbadmin=> SELECT * FROM dups2;
c1 | c2
----+----
1 | A
1 | A
1 | A
2 | B
3 | C
3 | C
4 | D
(7 rows)

dbadmin=> INSERT /*+ DIRECT */ INTO dups2 SELECT DISTINCT c1, c2 FROM dups;
OUTPUT
--------
4
(1 row)

dbadmin=> DELETE /*+ DIRECT */ FROM dups2 WHERE epoch IS NOT NULL;
OUTPUT
--------
7
(1 row)

dbadmin=> SELECT * FROM dups2;
c1 | c2
----+----
1 | A
2 | B
3 | C
4 | D
(4 rows)

dbadmin=> COMMIT;
COMMIT

This method works because the hidden table EPOCH column is NULL for each row inserted until you issue a COMMIT statement.

Share this post

Share this post to x. Share to linkedin. Mail to
OpenText avatar image

OpenText

OpenText, The Information Company, enables organizations to gain insight through market-leading information management solutions, powered by OpenText Cloud Editions.

See all posts

More from the author

Manutan combines digital services with the human touch to delight customers

Manutan combines digital services with the human touch to delight customers

At Manutan, we equip businesses and communities with the products and services they require to succeed. Headquartered in France, our company has three divisions, serving…

4 minute read

Reaching new markets in Europe and beyond

Reaching new markets in Europe and beyond

How information management specialists at One Fox slashed time to market for innovative products with OpenText Cloud Platform Services At One Fox, we’ve driven some…

4 minute read

SoluSoft helps government agencies tackle fraud faster

SoluSoft helps government agencies tackle fraud faster

Fraud, in all its forms, is a pervasive problem, spanning industries and preying on vulnerabilities in federal and state government systems. Each year in the…

3 minute read

Stay in the loop!

Get our most popular content delivered monthly to your inbox.