Re: Create PostgreSQL Database with ENCODING 'WIN1252' on Cluster Initialized with locale "en_US.UTF-8"?

KARIN SUSANNE HILBERT <ioh1(at)psu(dot)edu> writes:
> I have a Windows DB2 database that I need to migrate to PostgreSQL 9.4 on RedHat Linux.
> The database cluster was initialized with locale "en_US.UTF-8".

It's possible you need to rethink that choice of initial locale, but not
necessarily.

> For my migrated database, I need to create a PostgreSQL database with ENCODING 'WIN1252'.

Hm, do you really need the DB encoding to be that? If so, why? You could
run the database in UTF8 encoding and set the client_encoding to WIN1252
for clients that only speak that encoding. This would result in
on-the-fly encoding conversion while talking to such clients, but I don't
believe the cost of that is intolerable. An advantage is that you could
also cater to clients that want less Windows-centric encodings.

But the really core decision here, which you haven't given enough info to
know which way you need to jump on, is what locale behavior you want (eg,
what sorting order). I do not think that there are any WIN1252-aware
locales on Red Hat (or probably any other flavor of Linux), which means
that if you want to run the DB in WIN1252 server_encoding, the only locale
choice that will work is "C". Maybe that's fine for your purposes, in
which case you could do it like this:

CREATE DATABASE ... ENCODING 'WIN1252' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;

(You need the "TEMPLATE template0" incantation if you're selecting
encoding or collate or ctype different from the installation defaults.
This is fine for a one-off, but if you foresee doing it often enough to
get annoying, you could re-initdb and change the installation defaults.)

Alternatively, maybe you want the behaviors of en_US locale, such as
dictionary sort order. In that case you should just do "CREATE DATABASE"
with no special options, and instead look to setting client_encoding
appropriately for the clients that want to speak WIN1252.

For more info try reading

https://www.postgresql.org/docs/current/static/charset.html

regards, tom lane