PostgreSQL UPSERT: Insert or Update Rows

PostgreSQL Upsert: Insert or Update Simplified

Learn how to use the PostgreSQL UPSERT feature to insert new rows or update existing ones efficiently. See examples and best practices in action.

How to Use UPSERT in PostgreSQL?

The term UPSERT combines update and insert, enabling you to insert a new record if it doesn't exist or update the existing one if it does.

Starting from PostgreSQL 9.5, UPSERT is achieved with the ON CONFLICT clause.

1. Basic UPSERT Syntax

INSERT INTO table_name (column1, column2, ...)  
VALUES (value1, value2, ...)  
ON CONFLICT (conflict_column)  
DO UPDATE SET column1 = value1, column2 = value2;   

Here:

• conflict_column: The column(s) that triggers a conflict (e.g., primary key).
• DO UPDATE: Specifies how to update the existing record.

2. Example: UPSERT with Primary Key Conflict

Consider a users table:

To insert a new user or update an existing one based on the id:

Code:

INSERT INTO users (id, name, email)  
VALUES (1, 'Abiola Updated', ‘[email protected]')  
ON CONFLICT (id)  
DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;  
The EXCLUDED keyword refers to the new values provided in the INSERT statement.

3. UPSERT without Updating all Columns

If you only want to update the email column:

Code:

INSERT INTO users (id, name, email)  
VALUES (1, 'Abiola Laila', '[email protected]')  
ON CONFLICT (id)  
DO UPDATE SET email = EXCLUDED.email;  

4. UPSERT with Unique Constraints

If your table has a unique constraint (e.g., on email), you can handle conflicts using it:

Code:

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

5. Insert Without Updating on Conflict

If you want to skip updates entirely in case of conflicts:

Code:

INSERT INTO users (id, name, email)  
VALUES (1, 'Abiola Laila', '[email protected]')  
ON CONFLICT (id)  
DO NOTHING;    

Best Practices

• Use Proper Indexing: Ensure your conflict column has the appropriate constraint (e.g., PRIMARY KEY or UNIQUE).
• Log Updates: Keep a record of updates for audit purposes.
• Avoid Complex Updates: UPSERT operations should be simple for performance reasons.

All PostgreSQL Questions, Answers, and Code Snippets Collection.