I am using Postgresql 15. I have two tables with example table structure. The table mytable has many data but the other table mycopy is empty. The mycopy table has an extra unique constrain on one column. I want to copy all data from mytable to mycopy table. But the data in mytable has duplicate value on the unique constrain column for mycopy table. How can I copy the data but skip those duplicate rows based on that column? But below statement will stop if there is a constrain error during insert.

INSERT INTO mycopy
SELECT * FROM mytable;

I am using Postgresql 15. I have two tables with example table structure. The table mytable has many data but the other table mycopy is empty. The mycopy table has an extra unique constrain on one column. I want to copy all data from mytable to mycopy table. But the data in mytable has duplicate value on the unique constrain column for mycopy table. How can I copy the data but skip those duplicate rows based on that column? But below statement will stop if there is a constrain error during insert.

INSERT INTO mycopy
SELECT * FROM mytable;

• postgresql

• 1 So just SELECT DISTINCT ON (the_column) * FROM mytable? – Bergi Commented Feb 7 at 23:39
• How do you know which duplicate you want to eliminate? – Adrian Klaver Commented Feb 7 at 23:42
• I don't mind which one get eliminated. – Joey Yi Zhao Commented Feb 8 at 2:13
• 2 Add ON CONFLICT DO NOTHING to the INSERT. Also, explicitly listing the columns is strongly recommended. – JohnH Commented Feb 8 at 2:48

1 Answer 1

The following will skip rows that violate unique key constraints:

INSERT INTO mycopy -- should list columns here and in the SELECT
SELECT * FROM mytable
ON CONFLICT DO NOTHING;

Refer to the PostgreSQL Documentation for additional details.

As the comment states, columns should be listed. Doing so protects against potential mismatched columns.