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.
👋 Check out our easy to use desktop GUI for SQL
Beekeeper Studio is a truly cross-platform SQL GUI with a clean, uncluttered interface. Write SQL, edit data, alter tables, and more!
Available for MacOS, Linux, and Windows.
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.