PostgreSQL 10.3中如何避免INSERT操作产生重复数据

Got it, let's break this down step by step since you need to safely run bulk inserts while ensuring no duplicates based on the combination of first, second, third, fourth, and fifth.

First off, the INSERT ... ON CONFLICT DO NOTHING syntax relies on a unique constraint or index to define what counts as a duplicate. Your current example table only has a primary key on id, so we need to add a unique constraint for that 5-column group first.

Step 1: Create the Unique Constraint #

Option 1: Strict Exact Match (Including NULLs) #

If your business logic considers two rows duplicates only when all 5 columns match exactly (note: SQL treats NULL as distinct from every value, including another NULL), run this:

ALTER TABLE example
ADD CONSTRAINT example_unique_combo UNIQUE (first, second, third, fourth, fifth);

PostgreSQL automatically creates a unique index for this constraint, which the ON CONFLICT clause will use to detect duplicates.

Option 2: Treat NULLs in third as Equal #

If you want rows with matching first, second, fourth, fifth and NULL in third to be considered duplicates (since NULL != NULL by default in SQL), use an expression-based constraint to normalize the NULL value:

ALTER TABLE example
ADD CONSTRAINT example_unique_combo_null_safe UNIQUE (
    first, 
    second, 
    COALESCE(third, ''),  -- Replace NULL with empty string for comparison
    fourth, 
    fifth
);

This way, any NULL in third is treated as an empty string, so two rows with NULL in third and matching other columns will trigger a conflict.

Step 2: Run Bulk Inserts with Conflict Handling #

Now you can use the INSERT ... ON CONFLICT DO NOTHING syntax for your bulk operations. Here's an example:

INSERT INTO example (first, second, third, fourth, fifth)
VALUES
    ('alice', 'jones', 'designer', 'UK', 'LON'),
    ('bob', 'brown', NULL, 'AU', 'SYD'),
    ('alice', 'jones', 'designer', 'UK', 'LON')  -- This duplicate will be skipped
ON CONFLICT (first, second, third, fourth, fifth) DO NOTHING;

If you used the null-safe constraint (Option 2), adjust the ON CONFLICT clause to match the expression:

ON CONFLICT (first, second, COALESCE(third, ''), fourth, fifth) DO NOTHING;

Quick Performance Tip #

For large bulk inserts, always group multiple rows into a single VALUES clause instead of running individual INSERT statements—it's way more efficient. The unique index will add a small overhead, but it's the standard, reliable way to enforce duplicate prevention here.

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