Upsert with ON CONFLICT / ON DUPLICATE KEY

Idempotent Bulk Inserts with Upsert

Upsert (INSERT or UPDATE) ensures your bulk import is idempotent — running it multiple times produces the same result. The tool generates dialect-specific upsert syntax.

PostgreSQL: ON CONFLICT DO UPDATE

INSERT INTO "products" ("sku", "name", "price", "stock")
VALUES
  ('SKU-001', 'Laptop', 999.99, 50),
  ('SKU-002', 'Mouse', 29.99, 200),
  ('SKU-003', 'Monitor', 599.99, 30)
ON CONFLICT ("sku") DO UPDATE SET
  "name" = EXCLUDED."name",
  "price" = EXCLUDED."price",
  "stock" = EXCLUDED."stock";

The EXCLUDED pseudo-table provides access to the values that were proposed for insertion.

MySQL: ON DUPLICATE KEY UPDATE

INSERT INTO \`products\` (\`sku\`, \`name\`, \`price\`, \`stock\`)
VALUES
  ('SKU-001', 'Laptop', 999.99, 50),
  ('SKU-002', 'Mouse', 29.99, 200)
ON DUPLICATE KEY UPDATE
  \`name\` = VALUES(\`name\`),
  \`price\` = VALUES(\`price\`),
  \`stock\` = VALUES(\`stock\`);

MySQL uses VALUES(column_name) to reference the proposed values.

How to Use

1. Set the ON CONFLICT option to "Upsert (DO UPDATE)"
2. Select the Conflict Column (usually the primary key or unique constraint column)
3. All other included columns will be listed in the UPDATE SET clause

When to Use Upsert

• Data synchronization: Import data from an external source that may contain existing records
• Idempotent scripts: Migration scripts that can be re-run safely
• Incremental imports: Daily data feeds that overlap with existing records