How to insert data in PostgreSQL only if it doesn’t exist?

PostgreSQL: INSERT if Row does not Exist

In PostgreSQL, you may want to insert a new row only if it doesn't already exist in the table, which can be helpful to avoid duplicate entries. This operation can be achieved using the INSERT ... ON CONFLICT statement or by using a subquery with conditional logic.

Using INSERT ... ON CONFLICT

PostgreSQL’s ON CONFLICT clause allows you to specify what action to take when a conflict occurs due to duplicate values in a unique constraint. You can specify DO NOTHING to skip the insertion if a conflict is detected.

Syntax for INSERT ... ON CONFLICT

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (unique_column) DO NOTHING;

Explanation:

• table_name: Name of the table you want to insert data into.
• (column1, column2, ...): Columns in the table to insert values.
• VALUES (value1, value2, ...): Values to be inserted into the columns.
• ON CONFLICT (unique_column) DO NOTHING: Skips the insertion if unique_column already has the specified value.

Example Code and Explanation:

Consider a table users where each user should have a unique email. Here’s how to insert a new user only if their email doesn’t already exist.

1. Using ON CONFLICT with DO NOTHING

Code:

-- Insert a user only if the email doesn't already exist
INSERT INTO users (user_id, name, email)
VALUES (1, 'Ianeira Adde', '[email protected]')
ON CONFLICT (email) DO NOTHING;

Explanation:

• INSERT INTO users (user_id, name, email): Specifies the table and columns to insert data.
• VALUES (1, 'Haralds Keiko', ' [email protected]'): Provides values for user_id, name, and email.
• ON CONFLICT (email) DO NOTHING: If the email already exists, the insertion is skipped.

2. Using ON CONFLICT with a Unique Constraint

First, ensure a unique constraint on the email column:

Code:

-- Ensure email column is unique
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

Then, insert the data with ON CONFLICT as shown above.

Alternate Method Using WHERE NOT EXISTS

Another method is to use a conditional WHERE NOT EXISTS clause with INSERT in a function or script. This is useful if you want to check for multiple conditions before inserting.

Code:

-- Insert a user if the email does not exist in the table
INSERT INTO users (user_id, name, email)
SELECT 2, 'Nessa Mele’, '[email protected]'
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE email = '[email protected]'
);

Explanation:

• INSERT INTO users (user_id, name, email): Specifies the target table and columns.
• SELECT 2, 'Jane Doe', '[email protected]': Attempts to insert this row.
• WHERE NOT EXISTS: Executes the INSERT only if the subquery finds no rows with the same email.

Important Notes:

• Primary Keys vs. Unique Constraints: The ON CONFLICT clause works with unique constraints or primary keys. Make sure the column you check for conflicts has a unique constraint.
• Performance: The ON CONFLICT method is generally more efficient than WHERE NOT EXISTS for single inserts.