PostgreSQL ON CONFLICT Statement
PostgreSQL ON CONFLICT Statement - Explained
📘 Introduction to ON CONFLICT Statement
In PostgreSQL, ON CONFLICT provides a powerful mechanism to deal with data clashes during INSERT operations. When inserting into a table that contains a UNIQUE constraint or index, you can define how the database should react—either by updating the existing record or ignoring the new one.
🧪 Syntax Overview
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
DO UPDATE SET column1 = value1, column2 = value2, ...;
conflict_target: typically the column(s) with a UNIQUE constraint (e.g., employee_id)
DO UPDATE: specifies what should be updated
DO NOTHING: skips the insertion if conflict occurs
🎯 Real-World Example
INSERT INTO employees (employee_id, name, salary)
VALUES (101, 'John Doe', 50000)
ON CONFLICT (employee_id)
DO UPDATE SET
name = EXCLUDED.name,
salary = EXCLUDED.salary;
If an employee with ID 101 already exists, this query updates their name and salary instead of inserting a duplicate.
✅ Benefits of Using ON CONFLICT
- Handles INSERT errors seamlessly
- Reduces the need for complex conditional queries
- Ensures data integrity through atomic operations
- Improves performance in bulk-upsert operations
(Like)
ReplyDelete