Re: pg_dump and money type

Tom Lane wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)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