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:
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
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4