How can I retry a write operation in DuckDB without causing duplicate rows?

Achieving Idempotency in DuckDB Writes

To retry a write operation in DuckDB without creating duplicate rows, you must define a UNIQUE or PRIMARY KEY constraint on the columns that identify a unique record. Without a constraint, DuckDB cannot distinguish between a legitimate duplicate and a retry of a previously successful operation.

Recommended Implementation Patterns

Depending on your version and desired behavior, use one of the following two patterns to ensure idempotency during retries:

1. The Conditional Insert (Safe Skip)

If you want to ignore the write if the data already exists, use an INSERT INTO ... SELECT pattern combined with a WHERE NOT EXISTS clause. This is the most portable method across DuckDB versions.

-- Ensure the table has a unique identifier
CREATE TABLE ingestion_log ( 
    request_id VARCHAR PRIMARY KEY, 
    payload TEXT, 
    created_at TIMESTAMP 
);

-- Idempotent insert logic
INSERT INTO ingestion_log 
SELECT 'req_123', 'sample data', now() 
WHERE NOT EXISTS (
    SELECT 1 FROM ingestion_log WHERE request_id = 'req_123'
);

2. The Upsert Pattern (Replace)

If the retry should update the existing record with the latest data rather than skipping it, use INSERT OR REPLACE. This requires a PRIMARY KEY or UNIQUE constraint to function.

INSERT OR REPLACE INTO ingestion_log 
VALUES ('req_123', 'updated sample data', now());

Handling Transient Failures

To prevent partial writes from complicating your retry logic, wrap your operations in an explicit transaction. This ensures that if a network glitch occurs mid-operation, the entire batch is rolled back, leaving the database in a clean state for the next retry attempt.

• Begin: BEGIN TRANSACTION;
• Execute: Run your idempotent INSERT.
• Commit: COMMIT;

Verification Steps

You can verify your retry logic is working by running the following sequence in your CLI or IDE:

1. Create a table with a PRIMARY KEY.
2. Execute your INSERT statement once.
3. Execute the exact same INSERT statement a second time.
4. Run SELECT COUNT(*) FROM table;. If the result is 1, your operation is idempotent.

Assumptions and Constraints

This approach assumes you have a natural or synthetic key (like a request_id or transaction_id) that remains constant across retries. If your data lacks a unique identifier, you must implement application-level deduplication or a staging table approach.

Diagnostic Detail Needed: Are you performing single-row inserts or bulk loading via CSV/Parquet? Bulk loading requires a different strategy, such as loading into a temporary table and performing a JOIN-based deduplication before the final merge.