PostgreSQL多值插入时跳过重复联合键的实现方案咨询

Got it, let's tackle this duplicate key problem you're dealing with. Since you're pulling id and title from my_table to insert into another_table (with key1 mapped to id and key2 fixed as '5555'), and key1 + key2 form a composite unique constraint, here are the most reliable ways to skip duplicates without triggering errors:

1. Use INSERT ... ON CONFLICT DO NOTHING (PostgreSQL 9.5+) #

This is the cleanest and most efficient method for modern PostgreSQL versions. It directly tells the database to ignore any rows that would violate the unique constraint.

First, make sure you have the composite unique constraint on another_table (if you don't already):

ALTER TABLE another_table ADD CONSTRAINT unique_key1_key2 UNIQUE (key1, key2);

Then use this insert statement that pulls data from my_table and skips duplicates:

INSERT INTO another_table (key1, key2, title)
SELECT id, '5555' AS key2, title
FROM my_table
ON CONFLICT (key1, key2) DO NOTHING;

Why this works: #

The ON CONFLICT (key1, key2) clause targets your composite unique constraint. When a row would create a duplicate (key1, key2) pair, DO NOTHING tells PostgreSQL to skip that row entirely instead of throwing an error.

2. Filter Duplicates with NOT EXISTS (For Older PostgreSQL Versions) #

If you're stuck on a PostgreSQL version before 9.5 (where ON CONFLICT wasn't introduced), use a NOT EXISTS subquery to pre-filter rows that already exist in another_table:

INSERT INTO another_table (key1, key2, title)
SELECT m.id, '5555' AS key2, m.title
FROM my_table m
WHERE NOT EXISTS (
    SELECT 1
    FROM another_table a
    WHERE a.key1 = m.id AND a.key2 = '5555'
);

Why this works: #

The subquery checks if a matching (key1, key2) pair already exists in another_table. Only rows from my_table that don't have a match get inserted.

Bonus: Handle Duplicates in my_table (If Needed) #

If my_table has duplicate id values (unlikely if id is a primary key, but possible), add DISTINCT to your select to avoid trying to insert duplicate keys from the source table itself:

INSERT INTO another_table (key1, key2, title)
SELECT DISTINCT id, '5555' AS key2, title
FROM my_table
ON CONFLICT (key1, key2) DO NOTHING;

内容的提问来源于stack exchange，提问作者hello_its_me