Re: DateStyle causes drama during upgrade

Andrew McMillan wrote:
>
> 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.

Well, that's helpful, if you know. Silly idea. But I guess the real
problem
is that it doesn't require the input to be of that format, silently
corrupting
data... Even just a warning would have made it clear where the problem
lay.

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

Nope, it's not:
kleptog//usr/share/locale>ls -d en*
en/  en_AU/  en_BW/  en_CA/  en_DK/  en_GB/  en_IE/  en_US/  en_ZW/

I guess en_AU is for you :)

--
Martijn van Oosterhout <kleptog@cupid.suninternet.com>
http://cupid.suninternet.com/~kleptog/