How to Implement "INSERT if not exists" Functionality in PostgreSQL
While PostgreSQL doesn't have a built-in INSERT IF NOT EXISTS
statement, you can accomplish this behavior using a combination of INSERT
and SELECT
with a WHERE NOT EXISTS
clause. This ensures that a new row is only inserted into a table if a matching row doesn't already exist.
Implementation:
Here's the general structure:
INSERT INTO your_table (column1, column2, ...)
SELECT value1, value2, ...
WHERE NOT EXISTS (
SELECT 1 FROM your_table
WHERE existing_column = check_value
);
Explanation:
INSERT INTO your_table (column1, column2, ...)
: This specifies the target table and the columns you want to insert values into.SELECT value1, value2, ...
: This defines the values you want to insert for each column.WHERE NOT EXISTS ( ... )
: This is the key part that checks for existing rows.SELECT 1 FROM your_table
: This subquery is used for efficiency. It simply returns a constant value (1 in this case) to indicate a row is found.WHERE existing_column = check_value
: This condition determines if a matching row already exists. Replaceexisting_column
with the actual column you want to check for uniqueness (e.g., primary key, unique constraint). Replacecheck_value
with the specific value to check against.
Example:
Suppose you have a table users
with columns id
(primary key) and username
. You want to insert a new user with username='alice'
only if it doesn't exist already.
INSERT INTO users (username)
SELECT 'alice'
WHERE NOT EXISTS (
SELECT 1 FROM users
WHERE username = 'alice'
);
Key Points:
- This approach avoids duplicate insertions based on the specified condition.
- If a matching row exists, the
INSERT
statement is skipped, and no new row is added. - You can adapt this pattern to check for uniqueness based on multiple columns as well, by modifying the
WHERE
clause in the subquery. - For more complex scenarios involving updates or returning information about existing or inserted rows, consider exploring
INSERT ... ON CONFLICT
(PostgreSQL 11+) or stored procedures.
INSERT INTO users (username)
SELECT 'bob'
WHERE NOT EXISTS (
SELECT 1 FROM users
WHERE username = 'bob'
);
This code attempts to insert a new user with the username 'bob'
. If a user with that username already exists, the INSERT
statement will be skipped.
Example 2: Multiple Column Check (Unique Email):
INSERT INTO customers (name, email)
SELECT 'Charlie', '[email protected]'
WHERE NOT EXISTS (
SELECT 1 FROM customers
WHERE name = 'Charlie' AND email = '[email protected]'
);
This code tries to insert a new customer named 'Charlie'
with the email '[email protected]'
. The WHERE
clause in the subquery checks for both name and email to ensure uniqueness.
Example 3: Returning Information (PostgreSQL 9.5+):
INSERT INTO products (name, price)
SELECT 'Headphones', 129.99
WHERE NOT EXISTS (
SELECT 1 FROM products
WHERE name = 'Headphones'
)
RETURNING id, name;
This code attempts to insert a new product named 'Headphones'
with the price 129.99
. If successful, it also retrieves and returns the newly inserted product's id
and name
using the RETURNING
clause (available in PostgreSQL 9.5 and later).
- This is the most robust and recommended approach for PostgreSQL 9.5 and later versions.
- It provides a more concise and efficient way to handle conflicts during insert operations.
- Syntax:
INSERT INTO your_table (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target) DO NOTHING;
ON CONFLICT
: Specifies what action to take when a conflict occurs (e.g., violating a unique constraint).conflict_target
: Defines the column(s) that determine the conflict condition. PostgreSQL can often infer this automatically.DO NOTHING
: Instructs PostgreSQL to skip the insertion if a conflict arises.
Stored Procedures:
- Create a stored procedure that checks for existing rows and performs the insertion conditionally.
- Syntax: (Example in PL/pgSQL)
CREATE OR REPLACE PROCEDURE insert_if_not_exists(
_column1 value_type,
_column2 value_type,
...
) AS $$
BEGIN
IF NOT EXISTS (
SELECT 1 FROM your_table
WHERE existing_column = _column1
) THEN
INSERT INTO your_table (column1, column2, ...)
VALUES (_column1, _column2, ...);
END IF;
END;
$$ LANGUAGE plpgsql;
- The procedure takes parameters for the columns you want to insert.
- It checks for existing rows using a similar approach as the
SELECT ... WHERE NOT EXISTS
method. - If no conflict exists, it performs the insertion.
Choosing the Right Method:
- For basic "INSERT if not exists" functionality in PostgreSQL 9.5 and later,
INSERT ... ON CONFLICT
is the preferred method due to its efficiency and robustness. - For older PostgreSQL versions or scenarios requiring additional logic beyond just checking for existing rows, the
SELECT ... WHERE NOT EXISTS
or stored procedures can be viable alternatives.
postgresql sql-insert upsert