Duplicate rows on a partition table with a primary key defined

Products

• VMware Tanzu Greenplum

Issue/Introduction

Duplicate primary keys and duplicate rows can be found in a table with a primary key defined if it has partitions.
The partition tables should have the primary key defined on the root partition and each of the child partitions.
The definition of the primary key can be missing if the table is restored from a pg_dump backup.

The pg_dump backup will contain a line similar to the following line to recreate the primary key:
ALTER TABLE ONLY <schema>.<tablename>
    ADD CONSTRAINT <tablename>_pk PRIMARY KEY (<col1>, <col2>, ...);
The "ONLY" parameter means that the primary key is defined on the root partition only and not the child partitions.

Environment

Product Version: 6.18

Resolution

Find and remove duplicate rows and duplicate primary keys.
SELECT <col1>, <col2>, <col3>, count(*) FROM <tablename> GROuP BY <col1>, <col2>, <col3> ORDER BY 4 DESC; 
The above example assumes that the primary key is (<col1>, <col2>, <col3>)

Drop and recreate the primary key.
ALTER TABLE <tablename> DROP CONSTRAINT <primary_key_name>;
ALTER TABLE <tablename> ADD CONSTRAINT <primary_key_name> PRIMARY KEY (<col1>, <col2>, <col3>);