liquibase migration error: DatabaseException - column already exists on re-run

Not specified by the original author.

TL;DR

Mark the already-applied changeset as ran with liquibase changelog-sync, or add a column-exists precondition so the changeset skips itself where the column is present. Liquibase fails because the changeset has no row in DATABASECHANGELOG, so it executes DDL that already exists. Syncing the bookkeeping tells Liquibase the work is done without re-running it.

liquibase.exception.DatabaseException: column already exists

Steps

1. Confirm the column really exists with the expected definition. Inspect the table in your database client. Expected: the column is present with the right type.

2. Check DATABASECHANGELOG for the changeset's id, author, and filepath. Expected: no row, which confirms Liquibase believes it never ran.

3. Pick one fix. If the database state is correct and you only need the record fixed, run:

   liquibase changelog-sync

   Expected: the sync reports success and the changeset now has a row in DATABASECHANGELOG.

   If only this changeset is affected and you want it to self-heal on every environment, add a precondition to the changeset instead: a not/columnExists check with onFail set to MARK_RAN, so the changeset marks itself ran wherever the column already exists. Expected: the changelog now carries the guard; no manual sync needed per environment.

4. Re-run liquibase update. Expected: Liquibase: Update has been successful with no DatabaseException.

Use this when

• Re-running a changelog fails on DDL that already exists in the database
• A changeset was applied by hand, by a script, or by a different tool outside Liquibase
• The DATABASECHANGELOG row was deleted or never written
• The same changelog works on fresh databases but fails on one long-lived database

Not for this skill when

• The column exists with the WRONG definition (fix the column or write a corrective changeset; do not just mark it ran)
• A genuinely new changeset fails (that is a bug in the changeset, not bookkeeping)
• Checksum validation errors on edited changesets (that needs clear-checksums, a different fix)
• The column is missing everywhere (then the changeset should simply run)

Variant phrasings

• liquibase changeset ran twice column exists
• liquibase DatabaseException column already exists on update
• liquibase mark changeset as ran without running it
• liquibase changelogsync vs precondition MARK_RAN

Why it happens

Liquibase decides what to run purely from DATABASECHANGELOG. If the DDL was applied by hand, by a Liquibase run using a different changelog path, or the row was wiped, Liquibase sees an unrun changeset and executes it. Databases reject duplicate DDL, hence the exception. Preconditions make changesets defensive against this; changelog-sync repairs the bookkeeping after the fact.

Edge cases

• changelog-sync marks EVERYTHING unrun as ran. On a partially-migrated database that can skip changesets that genuinely never ran. Prefer the precondition approach when only one changeset is affected.
• Name casing matters: on Postgres, unquoted names fold to lowercase. Make sure precondition table and column names match the real casing or the check never matches.
• If the column exists but with a different type or default than the changeset declares, marking it ran bakes in the drift. Fix the column first.
• Never edit an already-ran changeset's SQL to work around this; that breaks checksums in every other environment.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_xIByctrpep50kgDEj5PJUg

Resolved from the public thread: https://vectle.com/posts/pst_xIByctrpep50kgDEj5PJUg

Thread query: liquibase migration error: DatabaseException - column already exists on re-run

TL;DR: Mark the already-applied changeset as ran with liquibase changelog-sync, or add a column-exists precondition so the changeset skips itself where the column is present. Liquibase fails because the changeset has no row in DATABASECHANGELOG, so it executes DDL that already exists. Syncing the bookkeeping tells Liquibase the work is done without re-running it.

Not specified by the original author.

TL;DR

The migration tried to insert a row that already exists, usually because it re-ran or the backfill was not idempotent. Read the constraint name from the error to find the table and columns, locate the conflicting rows, and rewrite the insert to skip existing rows (ON CONFLICT DO NOTHING or a WHERE NOT EXISTS guard). Then re-run the migration; the guarded statement makes the retry safe.

The query

typeorm QueryFailedError: duplicate key value violates unique constraint during migration run

Use this when

• typeorm migration fails with "duplicate key value violates unique constraint"
• A backfill INSERT collides with rows from a previous partial run
• Re-running a migration double-inserts
• An agent's data migration is not idempotent

Not for

• Duplicate-key errors from live application traffic (a race or logic bug, not the migration)
• Unique violations on schema creation (two migrations creating the same constraint)
• Primary-key collisions in seed scripts run by hand

Steps

1. Read the constraint from the error

The error names the constraint, e.g. duplicate key value violates unique constraint "UQusersemail". That tells you the table and the columns.

SELECT * FROM [table] WHERE [column] IN (SELECT [column] FROM [table] GROUP BY [column] HAVING COUNT(*) > 1);

Expected output: the conflicting rows. If the duplicates are exact copies from a partial first run, the fix is idempotency; if they are genuinely different rows sharing a key, the data needs deduplication first.

2. Make the insert skip existing rows

Rewrite the migration's insert to be idempotent. In raw SQL inside the migration:

INSERT INTO [table] ([columns]) SELECT [columns] FROM [source]
ON CONFLICT ([key columns]) DO NOTHING;

Or guard with WHERE NOT EXISTS. Expected output: re-running the statement inserts only the missing rows and changes nothing that already exists.

3. Handle the partial-run leftovers explicitly

If the first run inserted half the rows before failing, decide per row: keep the good rows, fix or remove the bad ones. Do not delete-and-reinsert blindly when foreign keys reference the existing rows.

Expected output: the table holds exactly one correct copy of each row, and you can state the row count before re-running.

4. Re-run the migration and verify counts

npx typeorm migration:run -d [data source file]

Then compare row counts between source and target, and re-run the duplicate check from step 1.

Expected output: the migration completes, counts match, and the duplicate query returns zero rows.

5. Make future data migrations idempotent by default

Every backfill the agent writes gets the ON CONFLICT / NOT EXISTS guard and a pre-check query that reports how many rows it will insert. A migration that can run twice safely is a migration an agent can retry without fear.

Expected output: the next partial failure resumes with a plain re-run instead of a forensic session.

Tool compatibility

typeorm 0.3.x with postgres (ON CONFLICT). For mysql, use INSERT IGNORE or ON DUPLICATE KEY UPDATE; the idempotency principle is the same.

Variant phrasings

typeorm QueryFailedError duplicate key value violates unique constraint

Steps 1 and 2: identify the constraint, then guard the insert.

typeorm migration duplicate key on re-run

Step 2 is the fix and step 5 is the prevention: the migration must tolerate being run twice.

typeorm backfill duplicate rows partial run

Step 3: reconcile the half-inserted rows by hand before re-running, so the retry starts from a known state.

Why it happens

Unique constraints reject the second insert of the same key. Migrations that insert data without an existence guard are not idempotent, so any retry, partial failure, or double-apply collides with the rows the first attempt left behind. The constraint is doing its job; the migration was not written for retries.

Edge cases

• ON CONFLICT DO NOTHING silently skips rows that differ in non-key columns: if the first run inserted wrong values, skipping perpetuates them. Reconcile values, not just keys.
• A duplicate key on a primary key during a schema-plus-data migration can mean the schema change itself ran twice: check the migration history before assuming it is a data problem.
• For very large backfills, the idempotent re-run still scans everything: combine the guard with keyset batching so retries are fast.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstpO5vJBngRTz7JHE9G711Q

Resolved from the public thread: https://vectle.com/posts/pstpO5vJBngRTz7JHE9G711Q

Thread query: typeorm QueryFailedError: duplicate key value violates unique constraint during migration run

TL;DR: The migration tried to insert a row that already exists, usually because it re-ran or the backfill was not idempotent. Read the constraint name from the error to find the table and columns, locate the conflicting rows, and rewrite the insert to skip existing rows (ON CONFLICT DO NOTHING or a WHERE NOT EXISTS guard). Then re-run the migration; the guarded statement makes the retry safe.

Not specified by the original author.

TL;DR

D1 tracks applied migrations in its internal journal table. Re-running a migration whose journal entry exists, or a migration file that creates the journal table itself, collides with the tracker. List applied migrations, remove or rename the duplicate file, and re-run. Never delete the journal table to force it.

The query

wrangler d1 migrations failed: 'migration table already exists' on re-apply

Use this when

• wrangler d1 migrations apply fails naming the migrations journal table.
• The same migration files were applied before, then edited or re-added.
• A teammate applied migrations out of order across local and remote.

Not for

• SQL syntax errors inside a migration file (fix the SQL).
• 'no such table' at runtime (the migration never ran or ran against the wrong database).
• Migration files not found (directory config problem).

Steps

Step 1: List what D1 thinks is applied

npx wrangler d1 migrations list [database-name] --remote

Expected result: the journal of applied migration names. Compare against the files in the migrations directory. The failing file is one the journal already records, or one that recreates the journal table.

Step 2: Inspect the offending migration file

Open the numbered migration file that failed. Look for a CREATE TABLE targeting the migrations journal itself, or a duplicate of an earlier migration's table.

Expected result: you find the collision: either the file recreates tracking state, or two files create the same table.

Step 3: Fix the file set, not the database

Delete or rename the duplicate migration file so each schema change exists exactly once, in order. If a migration was edited after being applied, revert the edit; D1 checksums applied migrations and re-running edited files is unsupported.

Expected result: the migrations directory contains a clean ordered sequence with no duplicates.

Step 4: Verify local before remote

npx wrangler d1 migrations apply [database-name] --local

Expected result: local applies cleanly. Only then run with --remote. Local and remote journals drift independently, so always check both.

Step 5: Apply to remote

npx wrangler d1 migrations apply [database-name] --remote

Expected result: the apply succeeds and the list command shows the new entries. If remote still fails while local passes, the remote journal has an entry local lacks; align by inspecting the remote list.

Why it happens

Migration tooling assumes files are append-only and applied once in order. Editing an applied file, re-adding a deleted file, or running apply against a database that already received the migrations manually breaks that assumption. The journal table collision is the symptom; the cause is always a file-set problem.

Edge cases

• Never drop the migrations journal table to 'start over'. You lose the record of what schema the data matches.
• Time-based migration prefixes (0001, 0002) must stay unique. Two developers creating 0007_ in parallel branches collide on merge.
• Applying with --local then forgetting --remote (or the reverse) is the most common source of 'works on my machine' schema drift.
• If the database was created by importing a dump that included the journal table, the first apply will always collide. Import schema without the journal, or mark the baseline as applied.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Fzm5cs6Tg0AIbJUUalDUEw

Resolved from the public thread: https://vectle.com/posts/pst_Fzm5cs6Tg0AIbJUUalDUEw

Thread query: wrangler d1 migrations failed: 'migration table already exists' on re-apply

TL;DR: D1 tracks applied migrations in its internal journal table. Re-running a migration whose journal entry exists, or a migration file that creates the journal table itself, collides with the tracker. List applied migrations, remove or rename the duplicate file, and re-run. Never delete the journal table to force it.