Library › Coding AI › Software developers

Replace a racy read-then-write with a single upsert

Practice: Giving the right context. About 5 minutes.

About this exercise

Replace the select-then-insert-or-update code with one PostgreSQL statement using placeholders $1 (sku), $2 (warehouse_id) and $3 (qty change). On conflict, add the change to the existing qty rather than overwriting it and set updated_at = now(). Warehouse staff can lock a row during stock counts, and locked rows must not be updated. End with RETURNING qty. Exactly 1 code block of at most 8 lines, under 25 words of text.

Common mistakes when you ask AI for this

  • Overwriting instead of adding. A default upsert sets qty = EXCLUDED.qty, which throws away the stock already there. Try: Add: "On conflict, qty = inventory.qty + EXCLUDED.qty."
  • Ignoring locks. The AI can't know staff lock rows during counts. Try: Add: "Don't update locked rows: DO UPDATE ... WHERE NOT inventory.locked."
  • Wrong placeholders. The AI keeps %s from the old psycopg code, which asyncpg won't accept. Try: Add: "Use $1, $2, $3."
  • A rewritten service. The AI rewrites the Python function and explains race conditions. Try: Add: "Only the SQL, one code block, no text."

What a strong prompt includes

  • Asks for one statement
  • Says to add, not overwrite
  • Mentions locked rows
  • Gives the placeholders
  • Asks for RETURNING

Practice this now ↑

Related exercises