Re: pg_dump and money type

Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>> Since the money type has a locale dependent input and output format, there has 
>> to be some context saved when a database dump is created.  For example, if 
>> your environment uses a locale that uses the opposite point-vs-comma 
>> conventions from English (e.g., de_DE), then the following will fail to 
>> replicate the regression test database:
> 
>> pg_dump regression | psql foo
> 
>> The database regression has lc_monetary = C set, so this will produce C output 
>> piped into, say, de_DE input.
> 
>> The first problem appears to be that pg_dump --create ought to save the 
>> database-specific configuration settings.  pg_dumpall gets this right.  But 
>> secondly, lc_monetary ought to be saved at the top of the dump file, much 
>> like client_encoding.  Unfortunately, that would probably break portability 
>> of dump files between different operating systems.  Perhaps we can get away 
>> with fixing --create and documenting this.  But something ought to be done 
>> about this; otherwise using the money type introduces a risk of breaking 
>> backup or upgrade procedures.
> 
> This risk seems rather overstated, as it's unlikely that someone using
> money would choose to reload their data into a DB with a fundamentally
> incompatible locale setting.  

It doesn't sound unlikely at all to me. For example, people often use
C-locale for performance reasons, or because of ignorance of locale
issues. One scenario that seems particularly likely is to initialize and
load a database with en_US or C locale, and run like that for a few
weeks. After that, you notice that something's wrong, strings are sorted
in a funny way, etc. You realize that you're using the wrong locale, so
you take a backup with pg_dump, re-initdb with correct locale, and restore.

I haven't been following this thread closely; is there a work-around?

--  Heikki Linnakangas EnterpriseDB   http://www.enterprisedb.com