Let the database enforce identity

An upsert is only well-defined when the application can identify which row represents the same business item. Put that identity in a unique constraint; an application-side check followed by insert can race with another request.

INSERT INTO import_item (external_id, payload_hash, title)
VALUES (@external_id, @payload_hash, @title)
ON CONFLICT (external_id) DO UPDATE
SET payload_hash = EXCLUDED.payload_hash,
    title = EXCLUDED.title
WHERE import_item.payload_hash <> EXCLUDED.payload_hash
RETURNING id, payload_hash;

Decide what a replay means

A repeated import with the same external ID and same content may be an unchanged replay. A different payload can create a new revision or replace selected fields, depending on the contract. Do not silently treat every conflict as permission to overwrite.

If importing a question also creates revisions, options, tags, and an audit entry, those writes should commit together. A failed validation or persistence step must not leave a partial question. Keep transactions scoped to the business operation and rely on a unique constraint for concurrent duplicate protection.

SQL Server is not identical

SQL Server has MERGE, while PostgreSQL commonly uses INSERT ... ON CONFLICT. Similar intent does not mean identical locking or edge-case behavior. Explain the target database's constraint and transaction semantics rather than translating syntax mechanically.

Interview takeaway

State the stable key, duplicate policy, transaction boundary, and what the caller receives on insert, update, or unchanged replay.