Importing timestamps from MySQL to Postgres

Question

I'm migrating from MySQL to Postgres using mysqldump and psql and getting this error:

ERROR: date/time field value out of range: "0000-00-00 00:00:00" at character 52 STATEMENT: INSERT INTO "cron" VALUES (1,'2015-07-11 05:21:40','0000-00-00 00:00:00',2,58,'updateBid','plus',NULL,NULL),(2,'2015-07-11 05:21:40','0000-00-00

The MySQL table looks like

CREATE TABLE "cron" (
  "id" int(11) NOT NULL,
  "execute_after" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "executed_at" timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

The Postgres table looks like this:

CREATE TABLE "cron" (
  "id" int NOT NULL,
  "execute_after" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "executed_at" timestamp,

How can I automatically import this field?

I presume (from the fact that executed_at is nullable in Postgres, but not in MySQL) that you wish for such zero values to become NULL in Postgres? If so, you could either search/replace in the .sql file; or first update the data in MySQL before exporting (perhaps by copying the data into a temporary table if you don't wish to alter the original). — eggyal
– eggyal, Commented Sep 22, 2015 at 23:38

Chloe · Accepted Answer · 2015-09-22 23:37:16Z

1

Ok I solved it with sed:

mysqldump --compress --compatible postgresql --no-create-info --compact dbname | sed $'s/\'0000-00-00 00:00:00\'/NULL/g' | psql dbname

answered Sep 22, 2015 at 23:37

Chloe

26.5k45 gold badges211 silver badges380 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Importing timestamps from MySQL to Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related