Re: Problem with a date when restoring on postgresql 7.4.9 : date/time field value out of range

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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)