PostgreSQL: Conflicting primary key constraint when inserting from a SELECT query with JOIN
I'm wondering if anyone has experience with I'm building a feature where I'm maintaining legacy code that Hey everyone, I'm running into an issue that's driving me crazy. I'm working on a project and hit a roadblock. I'm working with a `unique_violation` behavior when I try to insert data into one table from a SELECT query that includes a JOIN with another table. I'm using PostgreSQL 13. I have two tables: `users` and `orders`. The `users` table has a primary key on `user_id`, and the `orders` table references this key as a foreign key. Here’s the SQL I wrote to insert data into the `orders` table: ```sql INSERT INTO orders (order_id, user_id, order_date) SELECT o.order_id, u.user_id, o.order_date FROM orders_source o JOIN users u ON o.email = u.email; ``` When I run this, I get the following behavior: ``` behavior: duplicate key value violates unique constraint "orders_pkey" DETAIL: Key (order_id)=(123) already exists. ``` I’ve checked the `orders_source` table and confirmed that there are indeed duplicate entries for the same `order_id`. I expected the INSERT to skip duplicates, but it seems to be trying to insert them anyway. I’ve also tried using `ON CONFLICT DO NOTHING`, but that results in an behavior too because the conflict is on `order_id`, which isn’t a unique identifier in the source data. Here’s the `ON CONFLICT` syntax I attempted: ```sql INSERT INTO orders (order_id, user_id, order_date) SELECT o.order_id, u.user_id, o.order_date FROM orders_source o JOIN users u ON o.email = u.email ON CONFLICT (order_id) DO NOTHING; ``` Now, it just silently skips those that conflict, but doesn’t report which rows were skipped, which makes it hard to debug. What’s the best way to handle this situation? Should I be cleaning the `orders_source` data beforehand, or is there a better way to perform the insert to avoid these conflicts? Any help would be appreciated! Has anyone else encountered this? My development environment is CentOS. I recently upgraded to Sql 3.9. My team is using Sql for this desktop app. What are your experiences with this? I'm working with Sql in a Docker container on Windows 10. Any feedback is welcome!