duckdb check constraint failed error "constraint failed: amount must be positive" on insert

TL;DR

Fix the violating rows before the insert, not the constraint: DuckDB rejects the whole insert when any row has a non-positive amount, so filter or correct the offending rows in the source query and keep the check constraint as the guard. A check constraint in DuckDB is evaluated per row at insert time and one bad row fails the statement, which is why the error appears even when most rows are valid.

duckdb check constraint failed error "constraint failed: amount must be positive" on insert

Use this when

• The check or run fails with this symptom: a DuckDB check constraint fails with constraint failed amount must be positive on insert.
• You are working on a DuckDB table with a check constraint on an amount column loaded by an insert or a copy and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A constraint that is wrong for the business data, such as refunds that legitimately post negative amounts, where the constraint itself needs a reviewed change.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Run the source query with a filter for amount at or below zero and count the matches. Verify: the violating rows are counted and visible
2. Inspect a few violating rows for their origin: sign flips, refund rows, or a unit error. Verify: the row pattern explains where they come from
3. Correct the source query or the upstream data so amounts are positive, or route legitimate negatives to the table designed for them. Verify: a pre-insert check returns zero violating rows
4. Rerun the insert. Verify: the statement completes and the row count matches the source
5. Add a pre-load validation query to the pipeline so the next bad batch is caught before the insert. Verify: the validation runs as part of the load

Variant phrasings

duckdb constraint failed amount must be positive insert

The shorter phrasing focused on the named failure.

duckdb check constraint failed on insert

The symptom phrasing a searcher tries next.

duckdb constraint violation amount positive fix

The variant naming the failing stage or tool directly.

Compatibility: DuckDB 0.9 and later, check constraints on tables and insert from select

Why it happens

Check constraints are the last line of defence, not a cleansing tool. Refund rows, reversed signs from a source extract, or a join that pulls an adjustment row into a payments table all produce non-positive amounts that the constraint correctly rejects.

Edge cases / pitfalls

• DuckDB fails the whole statement on the first violating row, so row counts after a failed insert are unchanged; do not assume a partial load happened.
• Casting or taking the absolute value to satisfy the constraint destroys the sign information refunds and reversals depend on.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstEQ5Z2fU-fgH_eEuK3zalw