DevEquiv

    MySQL INSERT IGNORE → PostgreSQL ON CONFLICT DO NOTHING

    Why PostgreSQL's ON CONFLICT DO NOTHING requires naming the specific conflict target that INSERT IGNORE silently ignores errors for.

    HIGH

    Quick answer

    INSERT IGNORE silently skips any row that would violate any constraint on the table. PostgreSQL requires ON CONFLICT (column_or_constraint) DO NOTHING, which only applies to conflicts on that specific unique constraint or index — you must identify which one INSERT IGNORE was actually protecting against.

    Why it breaks

    MySQL's INSERT IGNORE is a blanket "suppress any row-level error" modifier — duplicate keys, but also some data-truncation and type-conversion warnings, depending on SQL mode. PostgreSQL's ON CONFLICT clause only ever handles conflicts on a specific named unique/exclusion constraint; it has no equivalent "ignore literally any error" mode, and it does nothing at all for non-uniqueness errors like a NOT NULL violation.

    Real examples

    Basic duplicate-key suppression

    MySQL 
    INSERT IGNORE INTO users (email, name) VALUES ('[email protected]', 'Ada');
    PostgreSQL 
    INSERT INTO users (email, name) VALUES ('[email protected]', 'Ada')
ON CONFLICT (email) DO NOTHING;
    The conflict target (email) must be an existing unique constraint or index — PostgreSQL will error if no such constraint exists on that column.

    What does NOT translate: non-uniqueness errors

    MySQL 
    -- INSERT IGNORE also suppresses some data-truncation warnings depending on sql_mode
    PostgreSQL 
    -- ON CONFLICT has no equivalent for this — validate/clean data before the INSERT instead
    This is a real behavioral gap, not a missing syntax feature — plan for it during data cleanup, not at the SQL-translation step.

    Safe migration options

    • Identify the unique constraint (usually the primary key or a UNIQUE index) that the source INSERT IGNORE was really guarding against duplicate inserts for.
    • If INSERT IGNORE was also relying on suppressing NOT NULL or type-conversion errors, that behavior has no PostgreSQL equivalent at all — those rows need to be cleaned or validated before the insert, not silently accepted.
    • Test with real duplicate data before cutover: an incorrect or missing conflict target means ON CONFLICT simply won't catch the conflict, and the insert will error instead of silently skipping — the opposite of what the code expects.

    PostgreSQL solution

    INSERT INTO table (...) VALUES (...) ON CONFLICT (unique_column) DO NOTHING;

    Validation

    Before cutover

    Attempt to insert a row you know duplicates an existing unique key and confirm it is silently skipped (no error, no row inserted) — then attempt to insert a row violating a different constraint (e.g. NOT NULL) and confirm it still correctly errors, since ON CONFLICT does not suppress that.

    Scan your own schema

    This page documents detector rule INSERT_IGNORE in the DevEquiv analyzer — paste or upload your real MySQL/MariaDB schema to find every occurrence of this (and 19 other documented issues) automatically, with a prioritized readiness report.

    Analyze a schema free

    References

    • MySQL Manual — INSERT Statement (v8.0) — checked 2026-07-30
    • PostgreSQL Manual — INSERT ... ON CONFLICT (v17) — checked 2026-07-30

    Last verified 2026-07-30.