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.
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.