How to create database with default system locale is set to et_EE.UTF-8

On Wednesday, December 21, 2011 10:28:24 am Andrus wrote:

In fresh Debian installation default system locale is set to et_EE.UTF-8
using

dpkg-reconfigure locales

Postgres is installed using

apt-get update
apt-get -t squeeze-backports install postgresql-9.1 postgresql-common
postgresql-contrib

Trying to create database with et_EE.UTF-8 collation and character type
returns error

---------------------------
pgAdmin III
---------------------------
An error has occurred:

ERROR: new collation (et_EE.UTF-8) is incompatible with the collation of
the template database (en_US.UTF-8) HINT: Use the same collation as in
the template database, or use template0 as template.

^^^^^^^^^^^^^^^^^^
See below for more info.

How to create new database with et_EE.UTF-8 collation and character type ?

http://www.postgresql.org/docs/9.1/interactive/multibyte.html
22.3.2. Setting the Character Set

Notice that the above commands specify copying the template0 database. When
copying any other database, the encoding and locale settings cannot be changed
from those of the source database, because that might result in corrupt data.
For more information see Section 21.3."

From Section 21.3:
"To create a database by copying template0, use:

CREATE DATABASE dbname TEMPLATE template0;
"

How to force Postgres installation to create template1 with et_EE.UTF-8
collation and character type ?

Andrus.

--
Adrian Klaver
adrian.klaver@gmail.com

Adrian and Bèrto,

Thank you very much for quick and excellent replies. Locale names are
different in every Linux distro.
Postgresql does not provide any way to retrieve them (ssh access is reqired
to retireve them using locale -a)

Thus suggection using hard coded locale names is not possible.

How to force server to use et_EE.UTF-8 as default locale without hard coding
it into application?

How to force command

CREATE DATABASE <yourdbname> TEMPLATE = template0

to use et_EE.UTF-8 locale by default ?

Andrus.

Hi!

How to force command

CREATE DATABASE <yourdbname> TEMPLATE = template0

to use et_EE.UTF-8 locale by default ?

http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html (see
last comment), I haven't checked it myself as I usually have a mix of
locales in my installs (often even in a single db) and never really used
any "default", but it should still work.

Bèrto

--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

On Thursday, December 22, 2011 8:29:11 am Andrus wrote:

Adrian and Bèrto,

Thank you very much for quick and excellent replies. Locale names are
different in every Linux distro.
Postgresql does not provide any way to retrieve them (ssh access is reqired
to retireve them using locale -a)

Thus suggection using hard coded locale names is not possible.

How to force server to use et_EE.UTF-8 as default locale without hard
coding it into application?

What application?

How to force command

CREATE DATABASE <yourdbname> TEMPLATE = template0

to use et_EE.UTF-8 locale by default ?

Well you would use template0 as the TEMPLATE only if you wanted to CREATE a
database with different collation than that in template1(the default template for
the CREATE DATABASE command). So the question then is, why is the database
cluster being created with a collation of en_US.UTF-8 when the locale is
supposed to have been set to et_EE.UTF-8?

First are you sure that dpkg-reconfigure locales is actually resetting the
locale?

Second when you connect to the cluster with psql what does \l show for encoding
and collation?

Andrus.

--
Adrian Klaver
adrian.klaver@gmail.com

Is it reasonable to use commands

export LC_COLLATE='et_EE.UTF-8'
export LC_CTYPE='et_EE.UTF-8'
apt-get -t squeeze-backports install postgresql-9.1 postgresql-common
postgresql-contrib

Will this force et_EE.UTF-8 locale ?

Andrus.

http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html
(see last comment), I haven't checked it myself as I usually have a mix of
locales in my installs (often even in a single db) and never really used
any "default", but it should still work.

using template1 requires exclusive access to cluster.
I cannot force all users to log out while creating new db.
So using template1 is not possible.

Andrus.

Is it reasonable to use commands

export LC_COLLATE='et_EE.UTF-8'
export LC_CTYPE='et_EE.UTF-8'

apt-get -t squeeze-backports install postgresql-9.1 postgresql-common
postgresql-contrib

Hmmm no, not really. If your problem is that the system locale is wrong for
your needs, you're going to have this thing popping up again and again (not
just in postgres). I'd rather make sure the locale of my system is what
it's expected to be, if I was you. Or do you have any particular reason to
use a locale at system level, and another at postgresql level? It does look
weird to me.

Besides, if I wanted to change a user locale in gentoo, I'd rather do
export LANG="et_EE.UTF-8"
export LC_COLLATE="C"

I have no idea of how binary distros would do that, but I'd expect them to
be able not to type the same value twice. So, in case this really is a
requirement, you really want to check your distro for details.

Bèrto
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

Hi!

using template1 requires exclusive access to cluster.
I cannot force all users to log out while creating new db.
So using template1 is not possible.

IMHO you really misunderstood the manual. The exclusive access is NOT to
the entire cluster, but to the template. Which in turn means that if you
ever use an existing database (not a template) to create another with a
copy procedure you definitely need to shut down this single database.

I'm creating stuff based on template1 (and a couple of internally produced
templates) ~once a month, and I never ever had to ask people to log out.
The only such issue is if and when you create a database, you use it, have
people logged in, and then decide "wouldn't it be wonderful to use it as a
template?". In that case yes, you have to require them to log out while you
copy it to, say, template9. But from then on you create based on template9,
and your previous db still remains a normal database.

You do want to read about templates and make a few experiments, I should
say.

Bèrto
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.

What application?

My application.

Well you would use template0 as the TEMPLATE only if you wanted to CREATE a

database with different collation than that in template1(the default
template for

the CREATE DATABASE command). So the question then is, why is the database
cluster being created with a collation of en_US.UTF-8 when the locale is
supposed to have been set to et_EE.UTF-8?
First are you sure that dpkg-reconfigure locales is actually resetting the

locale?

dpkg-reconfigure locales
Generating locales (this might take a while)...
en_US.UTF-8... done
et_EE.UTF-8... done
Generation complete.
*** update-locale: Warning: LANGUAGE ("en_US:en") is not compatible with
LANG (et_EE.UTF-8). Disabling it.

Second when you connect to the cluster with psql what does \l show for
encoding
and collation?

psql postgres
psql (9.1.1)
Type "help" for help.

postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
-----------+------------+----------+-------------+-------------+-----------------------

postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres

Andrus.

On Thursday, December 22, 2011 9:45:16 am Andrus wrote:

dpkg-reconfigure locales
Generating locales (this might take a while)...
en_US.UTF-8... done
et_EE.UTF-8... done
Generation complete.
*** update-locale: Warning: LANGUAGE ("en_US:en") is not compatible with
LANG (et_EE.UTF-8). Disabling it.

Actually the interesting part would be what locale and locale -a show after
the above:)?

Andrus.

--
Adrian Klaver
adrian.klaver@gmail.com

Actually the interesting part would be what locale

locale
LANG=et_EE.UTF-8
LC_CTYPE="et_EE.UTF-8"
LC_NUMERIC="et_EE.UTF-8"
LC_TIME="et_EE.UTF-8"
LC_COLLATE="et_EE.UTF-8"
LC_MONETARY="et_EE.UTF-8"
LC_MESSAGES="et_EE.UTF-8"
LC_PAPER="et_EE.UTF-8"
LC_NAME="et_EE.UTF-8"
LC_ADDRESS="et_EE.UTF-8"
LC_TELEPHONE="et_EE.UTF-8"
LC_MEASUREMENT="et_EE.UTF-8"
LC_IDENTIFICATION="et_EE.UTF-8"
LC_ALL=

and locale -a show after the above:)?

locale -a
C
en_US.utf8
et_EE.utf8
POSIX

Andrus.

On Thursday, December 22, 2011 1:22:39 pm Andrus wrote:

Actually the interesting part would be what locale

locale
LANG=et_EE.UTF-8
LC_CTYPE="et_EE.UTF-8"
LC_NUMERIC="et_EE.UTF-8"
LC_TIME="et_EE.UTF-8"
LC_COLLATE="et_EE.UTF-8"
LC_MONETARY="et_EE.UTF-8"
LC_MESSAGES="et_EE.UTF-8"
LC_PAPER="et_EE.UTF-8"
LC_NAME="et_EE.UTF-8"
LC_ADDRESS="et_EE.UTF-8"
LC_TELEPHONE="et_EE.UTF-8"
LC_MEASUREMENT="et_EE.UTF-8"
LC_IDENTIFICATION="et_EE.UTF-8"
LC_ALL=

and locale -a show after the above:)?

locale -a
C
en_US.utf8
et_EE.utf8
POSIX

Would seem to be one of two things:
1) The initdb is being done before the locale is changed.
or
2) The installation is overriding the locale, though I find this one less
possible than 1.

Andrus.

--
Adrian Klaver
adrian.klaver@gmail.com

On tor, 2011-12-22 at 18:29 +0200, Andrus wrote:

How to force command

CREATE DATABASE <yourdbname> TEMPLATE = template0

to use et_EE.UTF-8 locale by default ?

If you don't want to re-initdb, you could just update the datctype and
datcollate columns of pg_database for template0.

If you want to re-initdb, check the pg_createcluster man page on Debian.

Would seem to be one of two things:
1) The initdb is being done before the locale is changed.
or
2) The installation is overriding the locale, though I find this one less
possible than 1.

Thank you.
How to re-configure Postresql db cluster so that uses Debian default system
locale?

Andrus.

On Friday, December 23, 2011 7:26:08 am Andrus wrote:

Would seem to be one of two things:
1) The initdb is being done before the locale is changed.
or
2) The installation is overriding the locale, though I find this one less
possible than 1.

Thank you.
How to re-configure Postresql db cluster so that uses Debian default system
locale?

See Peters answer.

Andrus.

--
Adrian Klaver
adrian.klaver@gmail.com

If you don't want to re-initdb, you could just update the datctype and
datcollate columns of pg_database for template0.

Thank you.
where to find sql update statement which does this ?
Is

update pg_database set datctype ='et_EE.UTF-8', datcollate ='et_EE.UTF-8'

best for this ?

template0 is read-only, how to connect to and update it using pgAdmin ?

Andrus.

On fre, 2011-12-23 at 17:32 +0200, Andrus wrote:

If you don't want to re-initdb, you could just update the datctype and
datcollate columns of pg_database for template0.

Thank you.
where to find sql update statement which does this ?
Is

update pg_database set datctype ='et_EE.UTF-8', datcollate ='et_EE.UTF-8'

best for this ?

add WHERE datname = 'template0'

template0 is read-only, how to connect to and update it using pgAdmin ?

pg_database is shared between all databases, so you can connect to any
database to do this.