admin管理员组

文章数量:1289424

I am trying to insert missing order_ids from the order_details table into the orders table while ensuring that duplicates are not inserted. However, despite using a LEFT JOIN with WHERE o.order_id IS NULL, I still get the following error:

ERROR: duplicate key value violates unique constraint "orders_pkey" SQL state: 23505 Detail: Key (order_id)=(10875) already exists.

Table Structures

orders Table (order_id is the Primary Key)

CREATE TABLE orders ( 
order_id INTEGER PRIMARY KEY, 
customer_id INTEGER REFERENCES customers(customer_id), 
order_date DATE );

order_details Table (order_id is a Foreign Key)

CREATE TABLE order_details ( 
order_detail_id SERIAL PRIMARY KEY, 
order_id INTEGER REFERENCES orders(order_id), 
product_id INTEGER REFERENCES products(product_id), 
quantity INTEGER );

Query I Am Using

INSERT INTO orders (order_id, customer_id, order_date) 
SELECT DISTINCT od.order_id, 
(SELECT customer_id FROM customers ORDER BY RANDOM() LIMIT 1) AS customer_id, 
NOW() - INTERVAL '1 day' * FLOOR(RANDOM() * 365) AS order_date 
FROM order_details od 
LEFT JOIN orders o 
ON od.order_id = o.order_id 
WHERE o.order_id IS NULL LIMIT 100;

What I Expected

  • This query should only insert order_ids that do not already exist in orders.

  • The LEFT JOIN with WHERE o.order_id IS NULL should filter out existing order_ids.

  • I expect up to 100 new rows to be inserted into orders, ensuring there are no duplicates.

Any insights or fixes would be greatly appreciated!

本文标签：

版权声明：本文标题：sql - INSERT INTO Query Causes "Duplicate Key Violates Unique Constraint" Error Even With WHERE NOT EXISTS - S 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1741445027a2379145.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。