I'm converting a MySQL database to PostgreSQL. Two of the tables have DATE
columns which have many "partial" dates. For example, a partial date may be for
the publication date of a book, where the date is specified as only the year,
e.g., 1957-00-00, and another partial date may be the publication date of a
periodical specified as a month and year, e.g., 2005-03-00. MySQL accepts these
apparently invalid or incomplete dates, but when I try to copy them into
Postgres, I get errors like
ERROR: date/time field value out of range: "1997-10-00"
HINT: Perhaps you need a different "datestyle" setting.
CONTEXT: COPY Entry, line 1, column EntryDate: "1997-10-00"
I read Appendix B and section 8.5, but I didn't find any way around this, i.e.,
it seems Postgres insists on complete dates with no zero day of month or month.
Changing the zeros to ones would be major editing task and the application
code would still have trouble distinguishing whether 2005-03-01 meant March 2005
(a monthly publication date) or 1st March 2005 (a date of an article published
on that date or of a weekly periodical) (because right now it interprets the
zero day of month as the former). Any suggestions (aside from designing a new
datatype)?
Joe