Re: DateStyle causes drama during upgrade

Andrew McMillan wrote:
>
> Martijn van Oosterhout wrote:
> >
> > We used pg_dump in various ways, all with the date style "iso"
> > but always some of the dates appeared to be translated wrong.
> > Eventually we worked out that even though the datestyle was
> > set to "iso" on both machines, the old postgres read it as
> > "ISO with european conventions" whereas the new postgres read
> > it as "ISO with US conventions".
>
> > This is the postgresql debian package 7.0.2-3.
> >
> > PS. I thought we'd left behind all the US/non-US datestyle
> > distinction when we all started using ISO format (yyyy-mm-dd).
> > That was somewhat naive of me, huh?
>
> I've been bitten by this too. It seems that there are two
> characteristics for the dates: format (for output) and 'conventions' for
> input, and that 6.5 -> 7.0 changed from defaulting to European
> conventions to US conventions.
>
> I suspect this is Debian specific.
>
> Perhaps there should be a way of setting the conventions side of things
> in the /etc/postgresql/postmaster.init like there is a way of setting
> the format?

Ah! I found out now!

If you set the local in your /etc/postgresql/postmaster.init to an
appropriate one, it nearly gets it right.

If I set:
LANG=en_GB

I get european conventions, but if I leave it unset (the default) I get
US conventions.

Of course, if I set it for 'en_NZ' I get US conventions. Perhaps en_NZ
is not valid?

Cheers,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew(at)cat-it(dot)co(dot)nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267