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:
id | name | |
---|---|---|
1 | Abiola Laila | [email protected] |
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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/snippets/postgresql-upsert.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics