2

When using pgloader to move tables from MySQL to Postgres, one issue I'm running into is certain source MySQL tables can contain either NULL or 0000-00-00 00:00:00 values in the datetime fields, and conversion on some of those columns is proving to be problematic.

An example error I get regularly is: ERROR Database error 23502: null value in column "created_at" violates not-null constraint.

Given that the value needs to be non-null, what would be the ideal CAST statement? I've used: --cast "type date drop not null drop default using zero-dates-to-null", which if I understand correctly casts to a NULL value in Postgres. Is there a keyword to convert to a specific date or non-null value? Perhaps a saner approach?

I've contemplated modifying the source tables so their dates are something like epoch time, but that's a temporary hack at best, and will change some reports based on the MySQL tables.

Sena Heydari
  • 63
  • 6

1 Answers1

0

I think you already know you have two options:

  • Update MySQL data to some real value where it's 0000-00-00 00:00:00 or NULL, before you export data. You shouldn't use any special value as a substitute for NULL.

  • Allow NULL in your PostgreSQL database, and import the data with zero datetime values as NULL.

NULL is a legitimate way to represent missing or inapplicable data.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for the response, Bill! I ended up being able to find a middle ground with the Data team to convert all NULL's on the MySQL side to 0000-00-00 00:00:00 and convert from there. Appreciate the sanity check. – Sena Heydari Apr 03 '18 at 20:51