Mastering PostgreSQL ON CONFLICT DO NOTHING

PostgreSQL: ON CONFLICT DO NOTHING

The ON CONFLICT DO NOTHING clause in PostgreSQL allows you to handle conflicts that arise when attempting to insert data into a table with unique constraints or primary keys. Instead of throwing an error when a conflict occurs, the database ignores the conflicting row, ensuring smooth data handling.

This guide covers the syntax, use cases, examples, and best practices for using ON CONFLICT DO NOTHING.

Syntax:

 
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT [conflict_target] DO NOTHING;

Key Elements:

• conflict_target: Specifies the columns or constraints to watch for conflicts (e.g., unique constraints or primary keys).
• DO NOTHING: Skips the insertion of the conflicting row.

Example 1: Basic Usage with Unique Constraint

Code:

-- Create a table with a unique constraint
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY, -- Auto-incremented primary key
    email VARCHAR(255) UNIQUE -- Unique constraint on the email column
);

-- Insert a row into the users table
INSERT INTO users (email) 
VALUES ('[email protected]');

-- Attempt to insert the same email, but avoid an error
INSERT INTO users (email) 
VALUES ('[email protected]')
ON CONFLICT DO NOTHING;

Explanation:

1. The users table has a UNIQUE constraint on the email column.

2. The first INSERT successfully adds the email.

3. The second INSERT attempts to add the same email. Instead of throwing an error, the ON CONFLICT DO NOTHING clause ensures no action is taken for the conflicting row.

Example 2: Specifying Conflict Targets

Code:

-- Create a table with composite unique constraints
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY, -- Primary key for the table
    product_id INT NOT NULL, -- Product identifier
    customer_id INT NOT NULL, -- Customer identifier
    UNIQUE (product_id, customer_id) -- Composite unique constraint
);

-- Insert an order
INSERT INTO orders (product_id, customer_id) 
VALUES (1, 100);

-- Attempt to insert the same product-customer pair
INSERT INTO orders (product_id, customer_id) 
VALUES (1, 100)
ON CONFLICT (product_id, customer_id) DO NOTHING;

Explanation

• A composite unique constraint is defined on product_id and customer_id.
• The second INSERT detects a conflict with the existing pair and skips the insertion.

Output

Initial Table (Before Conflict):

order_id	product_id	customer_id
1	1	100

After Attempted Conflict Insert:

order_id	product_id	customer_id
1	1	100

Advantages of ON CONFLICT DO NOTHING

1. Error Prevention:

• Avoids errors caused by duplicate entries.

2. Simplicity:

• Allows you to skip the insertion logic for conflicts without adding complex checks.

3. Performance:

• Reduces unnecessary database operations in scenarios with frequent potential conflicts.

Common Use Cases

1. Data Synchronization:

• Importing data from external sources where duplicates are common.

2. Event Logging:

• Avoid duplicate event entries in audit logs or tracking systems.

3. Batch Inserts:

• Handling large datasets with potential conflicts.

Potential Pitfalls

1. Unintended Data Loss:

• The conflicting row is ignored entirely, meaning no logging or indication of skipped rows.

2. Selective Conflicts:

• If you need partial conflict handling (e.g., updating some fields instead of skipping), use ON CONFLICT DO UPDATE.

Comparison: DO NOTHING vs DO UPDATE

• DO NOTHING: Skips the conflicting row entirely.
• DO UPDATE: Allows you to modify specific fields in the existing row during a conflict.

Example of DO UPDATE:

Code:

INSERT INTO users (email, name) 
VALUES ('[email protected]', 'Jana Isabel')
ON CONFLICT (email) 
DO UPDATE SET name = EXCLUDED.name;

Best Practices

1. Use with Specific Constraints:

• Always specify conflict targets to avoid ambiguity and ensure proper conflict resolution.

2. Combine with Logging:

• Implement logging mechanisms to track skipped rows for auditing purposes.

3. Validate Data:

• Perform pre-checks for data integrity to minimize conflicts.

All PostgreSQL Questions, Answers, and Code Snippets Collection.