MySQL Zero Dates ('0000-00-00') → PostgreSQL Migration

Why PostgreSQL rejects MySQL's '0000-00-00' sentinel date outright, and how to decide what it actually meant before migrating the data.

BLOCKER

Quick answer

PostgreSQL has no concept of a '0000-00-00' date — it isn't a valid date, full stop. Decide what the sentinel actually meant in your application (usually "no date yet") and migrate it to NULL, or a real placeholder date your application already handles specially.

Why it breaks

MySQL (outside strict SQL mode) historically accepted '0000-00-00' and '0000-00-00 00:00:00' as storable date/datetime values, used by many older applications as a "not set" sentinel instead of NULL. PostgreSQL's date/timestamp types only accept real calendar dates — there is no year zero, so any row or default carrying this value fails to load or insert as-is, with no direct substitute value.

Real examples

Column default

CREATE TABLE orders (
  shipped_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
);

CREATE TABLE orders (
  shipped_at TIMESTAMP
);
-- Nullable, no default — NULL now means "not shipped yet" instead of a fake date

Dropping the NOT NULL constraint and the fake default is usually the right call once the sentinel's real meaning ("hasn't happened yet") is made explicit as NULL.

Data cleanup before load

-- Existing MySQL rows contain shipped_at = '0000-00-00 00:00:00'

UPDATE orders SET shipped_at = NULL WHERE shipped_at = '0000-00-00 00:00:00';
-- Run this against the MySQL export/staging copy before loading into PostgreSQL

This transformation has to happen before or during load — PostgreSQL will reject the literal value outright, it cannot be inserted and cleaned up afterward.

Safe migration options

Migrate to NULL if the column is nullable and the application already treats "no date" as an absent value elsewhere — this is the cleanest option when it is available.
If the column is NOT NULL and can't be changed, use a real, deliberately-chosen placeholder your application logic already special-cases (e.g. an epoch date) — document this choice, since it is an application-level decision, not a database one.
Audit application code for direct comparisons against '0000-00-00' before cutover — every one of those comparisons needs to change to an IS NULL check or a comparison against whatever placeholder you chose.

PostgreSQL solution

There is no PostgreSQL syntax equivalent — this requires a one-time data transformation (UPDATE ... SET col = NULL WHERE col = '0000-00-00') during migration, decided per column.

Validation

Before cutover

Before migration, run SELECT COUNT(*) FROM table WHERE col = '0000-00-00'; against every date/datetime column in the source schema to find out how many rows are actually affected — do not assume this only shows up in one or two columns.

Scan your own schema

This page documents detector rule ZERO_DATE 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 — Zero Values Occur When Using Strict Mode (v8.0) — checked 2026-07-30
PostgreSQL Manual — Date/Time Types (v17) — checked 2026-07-30

Last verified 2026-07-30.