CODEWITHSUNDEEP
mysqlpostgresqldatetimeimport
Chloe
Chloe

Reputation: 26294

Importing timestamps from MySQL to Postgres

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?

Upvotes: 0

Views: 1758

Answers (1)

Chloe
Chloe

Reputation: 26294

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

Upvotes: 1

Related Questions