Often in PostgreSQL you may want to insert a record into a table only if it does not already exist.
You can use the following syntax to do so:
INSERT INTO products VALUES (006, 'C', '2024-09-22') ON CONFLICT DO NOTHING;
This particular example will attempt to insert a new record into the table named products with values for three specific columns in the table.
If this record already exists in the table then nothing will happen, i.e. the table will remain unchanged.
If this record does not already exist in the table then it will be inserted into the table.
By using the statement ON CONFLICT DO NOTHING, we specify that if the record already exists then it should not be inserted into the table and no error message should be shown.
The following example shows how to use this syntax in practice.
Note: You can also specify ON CONFLICT DO UPDATE if you would instead like to update the record in the table.
Example: How INSERT If Not Exists Already in PostgreSQL
Suppose that we create the following table named products that contains information about various products sold by some company on various dates:
-- create table named 'products'
CREATE TABLE products (
productID INTEGER PRIMARY KEY,
name TEXT,
date_sold DATE
);
-- add rows to the table
INSERT INTO products VALUES (0001, 'A', '2024-09-11');
INSERT INTO products VALUES (0002, 'A', '2024-09-12');
INSERT INTO products VALUES (0003, 'B', '2024-09-10');
INSERT INTO products VALUES (0004, 'B', '2024-09-15');
INSERT INTO products VALUES (0005, 'C', '2024-09-18');
INSERT INTO products VALUES (0006, 'C', '2024-09-22');
-- view all rows in table
SELECT * FROM products;
productid | name | date_sold
-----------+------+------------
1 | A | 2024-09-11
2 | A | 2024-09-12
3 | B | 2024-09-10
4 | B | 2024-09-15
5 | C | 2024-09-18
6 | C | 2024-09-22
Suppose that we attempt to insert a new record into the table with the following values:
- productID: 0007
- name: D
- date_sold: 2024-09-25
We can use the following syntax to do so:
-- attempt to add new row to the table
INSERT INTO products VALUES (0007, 'D', '2024-09-25') ON CONFLICT DO NOTHING;
-- view all rows in table
SELECT * FROM products;
productid | name | date_sold
-----------+------+------------
1 | A | 2024-09-11
2 | A | 2024-09-12
3 | B | 2024-09-10
4 | B | 2024-09-15
5 | C | 2024-09-18
6 | C | 2024-09-22
7 | D | 2024-09-25
We can see that the new record has been inserted into the table since it did not already exist.
However, suppose that we attempt to insert a new record into the table that does already exist:
-- attempt to add new row to the table
INSERT INTO products VALUES (0001, 'A', '2024-09-11') ON CONFLICT DO NOTHING;
-- view all rows in table
SELECT * FROM products;
productid | name | date_sold
-----------+------+------------
1 | A | 2024-09-11
2 | A | 2024-09-12
3 | B | 2024-09-10
4 | B | 2024-09-15
5 | C | 2024-09-18
6 | C | 2024-09-22
7 | D | 2024-09-25
We can see that the table has remained unchanged since this exact record already existed.
Notice that we also don’t receive any error message since we used the ON CONFLICT DO NOTHING statement.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to SELECT DISTINCT on Multiple Columns
PostgreSQL: How to Calculate Difference Between Rows
