Insert Row if Not Exists in Table in PostgreSQL

In PostgreSQL, you may encounter situations where you want to insert a row into a table only if it does not already exist. This can prevent duplicate records and maintain data consistency. PostgreSQL provides several ways to achieve this functionality, such as using the INSERT ... ON CONFLICT clause or a conditional INSERT statement with a SELECT query.

In this tutorial, we’ll explore how to insert a row into a table only if it doesn’t already exist, along with practical examples for different scenarios.

Method 1: Using INSERT … ON CONFLICT

The INSERT ... ON CONFLICT statement allows you to handle conflicts that occur when a duplicate key violation happens. You can specify an alternative action, such as skipping the insert or updating the existing row.

Syntax:

</>
Copy
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column)
DO NOTHING;

Example: Insert Row with ON CONFLICT

Let’s create a table named users with a unique constraint on the email column:

</>
Copy
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);
PostgreSQL - Insert Row if Not Exists in Table - Example Table

Insert a row into the table:

</>
Copy
INSERT INTO users (username, email)
VALUES ('Alice', 'alice@example.com');
PostgreSQL - Insert Row if Not Exists in Table - Method 1: Using INSERT ... ON CONFLICT

Attempt to insert another row with the same email, but handle the conflict:

</>
Copy
INSERT INTO users (username, email)
VALUES ('Duplicate Alice', 'alice@example.com')
ON CONFLICT (email)
DO NOTHING;

Query the table to verify the result:

</>
Copy
SELECT * FROM users;

Explanation: The ON CONFLICT clause prevents the second insert from adding a duplicate row. The existing row remains unchanged, and no error is raised.

Method 2: Using Conditional INSERT with NOT EXISTS

An alternative approach is to use a conditional INSERT statement with a NOT EXISTS subquery to check whether the row already exists.

Example: Insert Row with NOT EXISTS

Using the same users table, let’s insert a row only if an email doesn’t already exist:

</>
Copy
INSERT INTO users (username, email)
SELECT 'Bob', 'bob@example.com'
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE email = 'bob@example.com'
);
PostgreSQL - Insert Row if Not Exists in Table - Method 2: Using Conditional INSERT with NOT EXISTS - First Insert

Attempt to insert another row with an existing email:

</>
Copy
INSERT INTO users (username, email)
SELECT 'Duplicate Bob', 'bob@example.com'
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE email = 'bob@example.com'
);
PostgreSQL - Insert Row if Not Exists in Table - Method 2: Using Conditional INSERT with NOT EXISTS

Query the table to verify the result:

</>
Copy
SELECT * FROM users;

Result:

Explanation: The NOT EXISTS subquery ensures that a row is only inserted if no row with the specified email already exists. If the email exists, the INSERT statement does nothing.

Conclusion

PostgreSQL provides multiple ways to insert rows into a table only if they do not already exist. The INSERT ... ON CONFLICT clause is simple and efficient for handling duplicate key conflicts, while the NOT EXISTS approach offers more flexibility for complex conditions. Both methods help maintain data integrity and avoid duplicate records in your database.