Thread: Problem with a date when restoring on postgresql 7.4.9 : date/time field value out of range
Problem with a date when restoring on postgresql 7.4.9 : date/time field value out of range
From
Thomas Poindessous
Date:
Hello, I have a problem with my postgresql 7.4.9 server. I tried to restore a dump on the backup server (same version). I got this error : pg_restore: ERROR: date/time field value out of range: "0001-02-29 00:00:00 BC" I understand this error, but I can't understand why postgresql didn't refused to insert it on the main server. The faulty script is a php script which uses postgresql 7.4.13 library (on another server). I don't think this error is corrected on 7.4.13 server (so even if I upgrade I will get this error). How can I do to be sure that my dump can be restored without a problem ? TIA. -- Thomas Poindessous
Re: Problem with a date when restoring on postgresql 7.4.9 : date/time field value out of range
From
"Jim C. Nasby"
Date:
On Fri, Oct 06, 2006 at 05:15:55PM +0200, Thomas Poindessous wrote: > Hello, > > I have a problem with my postgresql 7.4.9 server. > > I tried to restore a dump on the backup server (same version). > > I got this error : > > pg_restore: ERROR: date/time field value out of range: "0001-02-29 > 00:00:00 BC" > > I understand this error, but I can't understand why postgresql didn't > refused to insert it on the main server. > > The faulty script is a php script which uses postgresql 7.4.13 library > (on another server). > > I don't think this error is corrected on 7.4.13 server (so even if I > upgrade I will get this error). > > How can I do to be sure that my dump can be restored without a problem ? This is something that was probably channged somewhere between the version of the database that the dump was taken on and the version you're trying to restore to. Your best bet is to restore the dump to a database of that original version, fix the data, dump again, then restore to the newer version. And while you're at it, you should look at upgrading to 8.0.x or 8.1.x (though if you're using UTF8 you can run into some invalid data issues there as well). -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)