date/time field value out of range: "0000-00-00 00:00:00"
Laravel app, just moved from MySQL to PostgreSQL. The full message as Laravel shows it:
SQLSTATE[22008]: Datetime field overflow: 7 ERROR: date/time field value out of range: "0000-00-00 00:00:00"
Why it happens
MySQL stores zero dates ('0000-00-00') when strict mode is off. Postgres has no such date, so the copy or the insert fails.
Fix
-- on MySQL, before copying (column must allow NULL):
UPDATE orders SET shipped_at = NULL WHERE shipped_at = '0000-00-00 00:00:00';
ALTER TABLE orders MODIFY due_on date NULL DEFAULT NULL;
UPDATE orders SET due_on = NULL WHERE due_on = '0000-00-00';
Tested on MariaDB 11.8. Also remove DEFAULT '0000-00-00' from the schema, or new rows bring the problem back.
The tested fix for every table at once (scripts, a Laravel command, before/after checks): Laravel Postgres Error Fix Pack ($19), item 05.
Several errors at once? Paste your log into the error matcher. Raw SQL with MySQL syntax: the free SQL translator. Not migrated yet: the free schema risk check finds this kind of problem before the switch. Planning the whole move: Migration Kit ($49, $34.30 with LAUNCH30 until Nov 5).
Other errors after MySQL to Postgres
duplicate key value violates unique constraint "users_pkey"column "users.name" must appear in the GROUP BY clause or be used in an aggregate functionvalue "3000000000" is out of range for type integeroperator does not exist: boolean = integeroperator does not exist: character varying = integercolumn "a@x.com" does not existsyntax error at or near "`"function ifnull(text, unknown) does not existinvalid byte sequence for encoding "UTF8": 0x00current transaction is aborted, commands ignored until end of transaction block(no error) search or login finds nothing: WHERE email LIKE ... returns 0 rows