A creepy story about dates. How to prevent it? - Mailing list pgsql-general

Hi.

I will explain my experience to prevent other to fall in the same problem.

We  upgraded from 7.2 to 7.3 few days ago. We reload the data via pg_dump an
psql -i <file_dumped>.

The pg_dump was made with PGDATESTYLE=SQL, European (dd/mm/yyyy).

When we load the database we didn't configure te pgdestyle yet, then it was
the default (mm/dd/yy). We populate the data successfully.

Some days later we catch on that some dates are correct and some not. Some
dates appears with day and month exchanged. In this case Postgres
flexibility  turn  against us.

The load proces did this:

    INSERT INTO taula VALUES('12/01/2003,..
    INSERT INTO taula VALUES('13/01/2003,..

    SELECT fecha FROM taula:

    fecha
    --------
    12/01/03
    01/13/03 !!!!!

I suggest that maybe the pg_dump has to store the datestyle in order to
prevent this problems, no?

Regards,


Conxita Marín