PostgreSQL ON CONFLICT Walkthrough

PostgreSQL is a popular open-source relational database management system. One of the features of PostgreSQL is the ability to define constraints on columns in a table to maintain the integrity of the data. For example, you can define a column as being unique to ensure that no two records in the table have the same value for that column.

However, what happens when you try to insert a new record into the table that violates a unique constraint? In PostgreSQL, you can use the ON CONFLICT clause to handle this situation. This clause specifies the action to be taken when a unique constraint is violated.

First, you need a constraint

To demonstrate the use of the ON CONFLICT clause, let’s consider a simple example. Suppose we have a table called “products” that stores information about different products. The table has the following structure:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    UNIQUE (name)
);

The “id” column is the primary key of the table and is automatically generated by the SERIAL data type. The “name” column is defined as being unique to ensure that no two products have the same name.

Insert data that violates the constraint

Now, suppose we want to insert a new product into the table. We can use the following SQL statement:

INSERT INTO products (name, price)
VALUES ('Product A', 19.99);

However, if there is already a product with the name “Product A” in the table, this statement will fail because of the unique constraint on the “name” column. In this case, we can use the ON CONFLICT clause to specify what action to take.

Here are some examples of using the ON CONFLICT clause:

On conflict - ignore the insert

To simply ignore the insertion and do nothing, we can use the following syntax:

INSERT INTO products (name, price)
VALUES ('Product A', 19.99)
ON CONFLICT (name) DO NOTHING;

On conflict - update the existing record

To update the existing record with the new values, we can use the following syntax:

INSERT INTO products (name, price)
VALUES ('Product A', 19.99)
ON CONFLICT (name) DO UPDATE
SET price = EXCLUDED.price;

In the second example, we are using the EXCLUDED keyword to reference the values that were attempted to be inserted. This allows us to update the existing record with the new values.

You can also specify which columns to update using the SET clause, as well as any conditions that must be met for the update to be performed using the WHERE clause.

PostgreSQL ON CONFLICT summary

In summary, the ON CONFLICT clause in PostgreSQL allows you to handle unique constraint violations in a controlled manner. This can be useful for ensuring the integrity of your data and for updating existing records with new information.