Fix "duplicate key value violates unique constraint" in PostgreSQL
ERROR: duplicate key value violates unique constraint "orders_pkey"
The table has a rule that a column, usually the id, must be unique. Your INSERT or UPDATE would create a second row with a value that's already taken, so Postgres blocks the change. The constraint name in quotes tells you which column it is: orders_pkey is the primary key of orders.
Why it happens
- Inserting a row with an id that already exists, often from copying or re-running an INSERT.
- Loading data that overlaps with rows already in the table.
- The source data itself contains the same key twice.
- A sequence that fell behind after rows were inserted with hand-picked ids.
How to fix it
1. Use an id that isn't taken
Fails
INSERT INTO orders (id, customer_id, amount, status, order_date) VALUES (115, 3, 49.00, 'pending', '2024-07-22');
Works
INSERT INTO orders (id, customer_id, amount, status, order_date) VALUES (116, 3, 49.00, 'pending', '2024-07-22'); SELECT id, customer_id, amount, status FROM orders ORDER BY id DESC LIMIT 3;
Order 115 already exists. In real tables the id usually comes from an identity column or sequence, so leave id out of the INSERT and let Postgres pick it.
2. Update the row if it exists (upsert)
Fails
INSERT INTO product_catalog (sku, name, price, updated_on)
VALUES ('KB-210', 'Mechanical keyboard', 79.00, '2024-06-01');Works
INSERT INTO product_catalog (sku, name, price, updated_on)
VALUES ('KB-210', 'Mechanical keyboard', 79.00, '2024-06-01')
ON CONFLICT (sku) DO UPDATE
SET price = EXCLUDED.price,
updated_on = EXCLUDED.updated_on;
SELECT * FROM product_catalog ORDER BY sku;ON CONFLICT (sku) catches the duplicate and updates the existing row instead. EXCLUDED means the row you tried to insert.
3. Skip rows that already exist
Fails
INSERT INTO product_catalog (sku, name, price, updated_on) SELECT UPPER(TRIM(sku)), name, price, feed_date FROM price_feed;
Works
INSERT INTO product_catalog (sku, name, price, updated_on) SELECT UPPER(TRIM(sku)), name, price, feed_date FROM price_feed ON CONFLICT (sku) DO NOTHING; SELECT * FROM product_catalog ORDER BY sku;
KB-210 and MN-270 are already in the catalog, so they're skipped and only the new products are added. If the source has the same key twice, remove duplicates first, for example with DISTINCT ON (sku).
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.