ON CONFLICT (Upsert) in PostgreSQL

PostgreSQL, a highly powerful and open-source object-relational database system, offers a diverse array of solutions to some of the most complex tasks faced by developers. One such invaluable feature it provides is the ON CONFLICT (Upsert) clause. This feature elegantly solves the problem of handling conflicts during an insert operation, thereby facilitating the process of maintaining and updating data in the database. To understand this feature more clearly and how it can be efficiently employed in your PostgreSQL tasks, this blog post aims to break it down to its fundamentals. Strap in for a deep dive into PostgreSQL's ON CONFLICT (Upsert).

Understanding ON CONFLICT (Upsert)

ON CONFLICT (Upsert) is a clause in PostgreSQL that helps manage a common issue during insert operations—when the record you're attempting to insert already exists. "Upsert" is a combination of "update" and "insert", implying that if a record already exists, then update it; otherwise, insert it. This PostgreSQL feature provides a simple, yet highly efficient way to handle these conflicts, reducing complexity and increasing productivity.

Let's start by understanding its syntax:

INSERT INTO table (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
DO NOTHING

or

INSERT INTO table (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
DO UPDATE SET column1 = value1, column2 = value2, ...

The conflict_target can be either a column or a set of columns with a unique or exclusion constraint, or can be a conflict action. DO NOTHING simply ignores the row that caused the conflict, while DO UPDATE updates the conflicting row.

Why Use ON CONFLICT (Upsert)

One may ask, why should we use ON CONFLICT (Upsert) when we can manually check if a record exists and then decide whether to update or insert? The answer is performance and atomicity.

Let's say you want to insert a row into the database. If you check for the record's existence first and then perform an insert or update operation, you're effectively making two round-trip calls to the database. On the other hand, using ON CONFLICT (Upsert), you can accomplish the same result in a single statement, thereby reducing the overall time taken.

Moreover, ON CONFLICT (Upsert) provides atomicity, which means that it treats the whole operation as a single unit, ensuring either total success or total failure. If a conflict occurs midway during the execution, the operation won't partially succeed—it will be rolled back entirely. This preserves the consistency and integrity of your database.

Practical Usage of ON CONFLICT (Upsert)

Now, let's see ON CONFLICT (Upsert) in action. Consider we have a products table:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name TEXT NOT NULL,
    quantity INT NOT NULL
);

You might want to insert a row into this table:

INSERT INTO products (product_id, product_name, quantity)
VALUES (1, 'Apple', 50);

But what if a product with product_id 1 already exists? This is where ON CONFLICT (Upsert) comes into play:

INSERT INTO products (product_id, product_name, quantity)
VALUES (1, 'Apple', 50)
ON CONFLICT (product_id)
DO NOTHING;

With this, if a product with product_id 1 already exists, PostgreSQL will simply ignore this row and. Now, if you want to update the existing row in case of a conflict, you can use DO UPDATE:

INSERT INTO products (product_id, product_name, quantity)
VALUES (1, 'Apple', 50)
ON CONFLICT (product_id)
DO UPDATE SET product_name = 'Apple', quantity = 50;

In the above statement, if a product with product_id 1 already exists, the product_name and quantity will be updated to 'Apple' and 50, respectively.

Understanding DO UPDATE and EXCLUDED

The DO UPDATE clause allows us to update the existing row in case of a conflict. It can reference the excluded row's values using the EXCLUDED keyword. EXCLUDED is a system table that holds the row proposed for insertion initially. For instance:

INSERT INTO products (product_id, product_name, quantity)
VALUES (1, 'Apple', 50)
ON CONFLICT (product_id)
DO UPDATE SET quantity = EXCLUDED.quantity;

Here, if a product with product_id 1 already exists, only the quantity will be updated to the quantity of the excluded row—50, in this case.

Dealing with Multiple Conflicting Columns

PostgreSQL's ON CONFLICT (Upsert) clause is highly flexible and can handle multiple conflicting columns as well. Suppose you have a users table where email and username are unique:

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(255) UNIQUE NOT NULL
);

You can use ON CONFLICT (Upsert) on multiple unique columns like this:

INSERT INTO users (email, username)
VALUES ('[email protected]', 'john')
ON CONFLICT (email, username)
DO NOTHING;

Or if you want to update the row in case of a conflict:

INSERT INTO users (email, username)
VALUES ('[email protected]', 'john')
ON CONFLICT (email, username)
DO UPDATE SET email = '[email protected]', username = 'john';

FAQ

Q: Can I use ON CONFLICT (Upsert) on non-unique columns?

No. ON CONFLICT (Upsert) only works on columns that have a unique constraint or are used in an exclusion constraint.

Q: What happens if the conflict action isn't specified?

If you don't specify a conflict action in ON CONFLICT (Upsert), PostgreSQL defaults to DO NOTHING.

Q: How can I return the inserted or updated row?

You can use the RETURNING clause to get the inserted or updated row. For instance:

INSERT INTO products (product_id, product_name, quantity)
VALUES (1, 'Apple', 50)
ON CONFLICT (product_id)
DO UPDATE SET quantity = EXCLUDED.quantity
RETURNING *;

Q: What is the difference between ON CONFLICT (Upsert) and manually checking for existence first?

ON CONFLICT (Upsert) improves performance by combining what could be two operations into one. Additionally, it provides atomicity, ensuring that the operation is treated as a single unit and maintaining the consistency and integrity of the database.

To conclude, PostgreSQL's ON CONFLICT (Upsert) is an essential tool in the developer's arsenal. It provides an efficient and elegant solution to manage conflicts during insert operations, enabling developers to write cleaner, more robust, and performant code. So, make sure to take full advantage of this feature in your PostgreSQLjourney.

Remember, like with every other tool or feature in technology, it is crucial to understand when and where to use it. PostgreSQL's ON CONFLICT (Upsert) is perfect for managing data conflicts. However, ensure that it is the best fit for your specific situation. Carefully review your database design, requirements, and constraints before deciding on its application. Mastering this feature will unquestionably make your life easier when dealing with PostgreSQL databases, but it's also important to get comfortable with the rest of the ecosystem. Dive deep into the PostgreSQL documentation and play around with different commands, queries, and functionalities.

In the world of databases and data management, every piece of knowledge and every skill you acquire brings you one step closer to being a better developer or database administrator. Keep learning, keep exploring, and keep building.

Remember, "Knowledge is power". Embrace the power of PostgreSQL and its rich feature set to supercharge your database handling capabilities.