Importing the same file twice

Import a file. Notice three rows have the wrong city. Fix them in the spreadsheet, export, import again — and now you have every row twice, including the ones that were already right.

That was the behaviour until this week. Import only ever inserted, which made a corrected file something to clean up after rather than something to simply re-import.

Telling the database what "the same row" means

The fix isn't clever, and it shouldn't be: you say which columns identify a row. An email address, a SKU, an order number — whatever is true of one row and no other.

Then you choose what happens when a row already exists:

• Skip it. Keep what's stored, ignore the incoming copy. Good when the file is a superset and you only want what's new.
• Overwrite it. Update the stored row's other columns from the file. Good when the file is the correction.

The columns you matched on are never themselves rewritten — they're the identity, not the payload, and rewriting them would change which row you'd matched.

The counting matters more than it sounds

Skipped rows are counted separately from failures. Re-importing a file where nothing changed reports a large number of skips and zero errors, which is exactly what happened — rather than a wall of failures that reads like something went wrong.

Your database has to agree

This only works if the columns you name have a unique index in your database. That's not a SchemaStack requirement; it's the only mechanism a database has for recognising a duplicate at write time. Without one, two identical rows are simply two rows.

On PostgreSQL, naming columns with no such index fails the batch — and rather than passing along "there is no unique or exclusion constraint matching the ON CONFLICT specification", which is accurate and almost useless to someone who picked a column in a dialog, the import says nothing was written, names the columns you chose, and says an index on them is what's missing.

Adding that index is a schema change you can make in SchemaStack, and it's worth doing regardless — it's what stops duplicates arriving through every other route too.

What it doesn't do (yet)

• It's an API feature today, not a dialog one. The import screen in the app has no match-column picker yet, so a file imported by clicking through the interface still only inserts. Setting conflictColumns and the conflict mode means calling the bulk endpoint directly.
• On MySQL the columns you name are not really used. PostgreSQL reconciles on exactly the columns you gave it. MySQL's ON DUPLICATE KEY UPDATE takes no conflict target at all, so it reconciles on any unique key on the table — the same outcome when there's one unique key, a surprise when there are several. It also means MySQL cannot tell you that your chosen columns lack an index: with nothing to validate against, rows that should have matched are simply inserted, and you get the duplicates this feature exists to prevent. The PostgreSQL safety net above has no MySQL equivalent.
• No "update only" mode. You can skip or overwrite existing rows, but there's no way to say "correct what's there and ignore anything new" — an unmatched row is always inserted. That would want a fourth mode.
• The match is exact. [email protected] and [email protected] are different rows unless your column collates them the same way. There's no trimming or case-folding on the way in; normalise in the file, or with a database-level collation.
• Nothing reports what changed. You get counts — inserted, skipped, failed — but not which rows were overwritten or what their previous values were. If that matters, export before importing.
• One thousand rows per request. Larger files are split by the importer; if you're calling the endpoint yourself, that batching is yours to do.

The CSV import guide covers the file format and the column mapping, and own the list is the argument for why the data being in a database you control is what makes re-importing safe in the first place.