PostgreSQL: Unexpected 'duplicate key' scenarios during bulk insert despite unique index
I've spent hours debugging this and I'm working with a frustrating scenario while trying to perform a bulk insert into my PostgreSQL database....... I have a table called `users` with a unique index on the `email` field. This is the structure of my table: ```sql CREATE TABLE users ( id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(100) ); ``` When I attempt to insert multiple records, I'm using the following SQL command: ```sql INSERT INTO users (email, name) VALUES ('user1@example.com', 'User One'), ('user2@example.com', 'User Two'), ('user1@example.com', 'User One Duplicate'); ``` I expect the second row with the duplicate email to be ignored or throw a warning, but instead, I get the behavior message: ``` behavior: duplicate key value violates unique constraint "users_email_key" DETAIL: Key (email)=(user1@example.com) already exists. ``` I tried wrapping the bulk insert in a transaction to manage the errors more gracefully, like this: ```sql BEGIN; INSERT INTO users (email, name) VALUES ('user1@example.com', 'User One'), ('user2@example.com', 'User Two'), ('user1@example.com', 'User One Duplicate'); COMMIT; ``` However, I still receive the same behavior and the transaction fails completely. Is there a way to handle this situation so that all valid rows are inserted while ignoring duplicates? I've also considered using `ON CONFLICT DO NOTHING` but I was unsure how to structure the command. I'm currently using PostgreSQL version 13.3. Any insight would be greatly appreciated. This issue appeared after updating to Sql LTS. I recently upgraded to Sql LTS. Is there a better approach? I've been using Sql for about a year now.