SQL
Efficiently Insert or Update Rows (Upsert) in PostgreSQL
Learn the SQL upsert pattern using PostgreSQL's `INSERT ... ON CONFLICT` statement to efficiently manage data, prevent duplicate entries, and ensure data integrity.
INSERT INTO products (product_code, product_name, price, stock_quantity)
VALUES ('PROD001', 'Widget X', 29.99, 100)
ON CONFLICT (product_code) DO UPDATE SET
product_name = EXCLUDED.product_name,
price = EXCLUDED.price,
stock_quantity = products.stock_quantity + EXCLUDED.stock_quantity, -- Increment stock
last_updated_at = NOW(); -- Assuming a timestamp column
How it works: The 'upsert' operation (a portmanteau of UPDATE and INSERT) allows you to atomically insert a row if it does not exist, or update it if it does. In PostgreSQL, this is achieved using `INSERT ... ON CONFLICT (target_column) DO UPDATE SET ...`. This is crucial for avoiding race conditions, ensuring data consistency when synchronizing data, or when handling idempotent data writes based on a unique constraint or primary key. For MySQL, a similar functionality is provided by `INSERT ... ON DUPLICATE KEY UPDATE`.