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. Replace existing_column with the actual column you want to check for uniqueness (e.g., primary key, unique constraint). Replace check_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.