Emulating "INSERT IGNORE" and "ON DUPLICATE KEY UPDATE" in PostgreSQL

Insert Ignore:

This means inserting a new row only if it doesn't violate any unique constraints (duplicate key).

• One approach is to use a combination of INSERT and NOT EXISTS:
  • You write an INSERT statement with your data.
  • Then you use NOT EXISTS to check if a row with the same unique value already exists in the table.
  • If it doesn't exist (NOT EXISTS), the insert happens normally.

On Duplicate Key Update:

This means updating an existing row if a new insert would violate a unique constraint.

• You can achieve this with a combination of INSERT and EXCEPTION:
  • Wrap your INSERT statement in a BEGIN...END block.
  • Inside the block, if a unique constraint violation (unique_violation) occurs during the insert, it triggers the EXCEPTION block.
  • The EXCEPTION block can then execute an UPDATE statement to modify the existing row with the conflicting unique value.

Here are some things to keep in mind:

• The NOT EXISTS approach might be simpler for inserts, but it can become complex for updates with multiple conditions.
• The EXCEPTION block with UPDATE can handle updates but might have performance implications compared to a single MERGE statement.

-- Sample table with unique constraint on "user_id"
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) UNIQUE
);

-- Insert data, ignoring duplicates on "username"
INSERT INTO users (user_id, username)
SELECT 10, 'john_doe'
WHERE NOT EXISTS (
  SELECT 1 FROM users WHERE username = 'john_doe'
);

This code snippet first creates a table users with a unique constraint on the username column. Then, it attempts to insert a new user with user_id 10 and username john_doe. The NOT EXISTS clause checks if a username john_doe already exists. If it doesn't exist, the insert is performed.

-- Sample table with unique constraint on "email"
CREATE TABLE accounts (
  user_id SERIAL PRIMARY KEY,
  email VARCHAR(100) UNIQUE,
  balance DECIMAL(10,2)
);

-- Insert with update on duplicate email
BEGIN;
  INSERT INTO accounts (email, balance)
  VALUES ('[email protected]', 50.00)
  -- Update balance if email already exists
  ON CONFLICT (email) DO UPDATE SET balance = accounts.balance + EXCLUDED.balance;
EXCEPTION WHEN unique_violation THEN
  UPDATE accounts
  SET balance = balance + EXCLUDED.balance
  FROM accounts
  WHERE email = EXCLUDED.email;
END;
-- Commit the transaction
COMMIT;

This code creates a table accounts with a unique constraint on the email column. It then attempts to insert a new account with email [email protected] and balance 50. The ON CONFLICT clause specifies what to do if a unique constraint violation occurs. Here, it updates the balance of the existing row with the same email by adding the new balance from the attempted insert (EXCLUDED.balance).

The EXCEPTION block is an alternative approach that catches the unique constraint violation (unique_violation) and executes an UPDATE statement directly. This update finds the existing row with the conflicting email and updates its balance similarly.

This approach utilizes PostgreSQL's built-in INSERT ... ON CONFLICT syntax. It allows you to specify what action to take when a unique constraint violation occurs during an insert.

Here's an example for "insert ignore":

-- Sample table with unique constraint on "product_id"
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(50) UNIQUE
);

-- Insert, ignoring duplicates on "name"
INSERT INTO products (product_id, name)
VALUES (200, 'T-Shirt')
ON CONFLICT (name) DO NOTHING;

This code attempts to insert a product with ID 200 and name "T-Shirt". If a product with the same name already exists, the ON CONFLICT clause instructs PostgreSQL to do nothing (DO NOTHING), essentially ignoring the insert for that row.

Rules:

PostgreSQL allows defining rules on tables. These rules automatically execute specific actions whenever an insert or update happens on the table.

Here's a simplified example for "on duplicate key update":

-- Sample table with unique constraint on "user_id"
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) UNIQUE
);

-- Rule to update on duplicate user_id
CREATE RULE update_existing_user AS ON INSERT TO users
WHERE EXISTS (
  SELECT 1 FROM users WHERE user_id = NEW.user_id
)
DO UPDATE SET username = NEW.username;

This code creates a rule named update_existing_user. Whenever an insert happens on the users table, the rule checks if a user with the same user_id (using NEW.user_id) already exists. If it does, the rule updates the existing user's username with the new value (NEW.username).

Remember that using rules can add complexity and might have performance implications compared to simpler approaches.

Choosing the right method:

The best approach depends on your specific needs and the complexity of your operations.

• For simple "insert ignore" scenarios, NOT EXISTS or ON CONFLICT DO NOTHING might be sufficient.
• For more complex update logic on duplicates, EXCEPTION blocks with UPDATE or user-defined functions might be necessary.
• Rules offer automation but can add complexity and have potential performance drawbacks.