Will PostgreSQL 8.4 allow having different encoding databases on a same PostgreSQL server ?

Hi,

Le jeudi 09 avril 2009 à 14:03:28, Bruno Baguette a écrit :

[...]
Currently, I have several PostgreSQL databases, some of them are using
LATIN1 encoding, some of them are using UTF-8 encoding.

In order to have theses two encoding, we had to install two PostgreSQL
server on two different ports. One is for LATIN1 databases and one is
for UTF-8 databases. (I known there is a workaround which allows to mix
several databases encoding them on a same PostgreSQL server, by
specifying "C" locale to initdb).

You can do better already on 8.3. You create all your databases with UTF-8 and
add client_encoding with LATIN1 for the ones that need this. A simple:

ALTER DATABASE dbX SET client_encoding TO latin1;

should work.

I've heard some rumors on freenode stating that PostgreSQL 8.4. will
allow to have several databases encoding. Did I understand right ?

You'll have the possibility to specify lc_ctype and lc_collate. See
http://developer.postgresql.org/pgdocs/postgres/sql-createdatabase.html for
more informations.

Regards.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

On Thu, Apr 9, 2009 at 6:03 AM, Bruno Baguette <bruno.baguette@gmail.com> wrote:

Hello !

Currently, I have several PostgreSQL databases, some of them are using
LATIN1 encoding, some of them are using UTF-8 encoding.

In order to have theses two encoding, we had to install two PostgreSQL
server on two different ports. One is for LATIN1 databases and one is for
UTF-8 databases. (I known there is a workaround which allows to mix several
databases encoding them on a same PostgreSQL server, by specifying "C"
locale to initdb).

I think you are misinformed. With pgsql 8.3:

smarlowe=# show lc_collate ;
lc_collate
-------------
en_US.UTF-8

\l
List of databases
Name | Owner | Encoding
-----------+----------+----------
postgres | postgres | UTF8
smarlowe | smarlowe | UTF8

create database test with encoding 'SQL_ASCII';
\l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
postgres | postgres | UTF8
smarlowe | smarlowe | UTF8
test | smarlowe | SQL_ASCII

Nevermind, I'm an idiot. yeah, for latin1 you'd need locale=C

On Thu, Apr 9, 2009 at 6:24 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Thu, Apr 9, 2009 at 6:03 AM, Bruno Baguette <bruno.baguette@gmail.com> wrote:

Hello !

Currently, I have several PostgreSQL databases, some of them are using
LATIN1 encoding, some of them are using UTF-8 encoding.

In order to have theses two encoding, we had to install two PostgreSQL
server on two different ports. One is for LATIN1 databases and one is for
UTF-8 databases. (I known there is a workaround which allows to mix several
databases encoding them on a same PostgreSQL server, by specifying "C"
locale to initdb).

I think you are misinformed.  With pgsql 8.3:

smarlowe=# show lc_collate ;
 lc_collate
-------------
 en_US.UTF-8

 \l
       List of databases
  Name    |  Owner   | Encoding
-----------+----------+----------
 postgres  | postgres | UTF8
 smarlowe  | smarlowe | UTF8

create database test with encoding 'SQL_ASCII';
 \l
       List of databases
  Name    |  Owner   | Encoding
-----------+----------+-----------
 postgres  | postgres | UTF8
 smarlowe  | smarlowe | UTF8
 test      | smarlowe | SQL_ASCII

--
When fascism comes to America, it will be the intolerant selling it as
diversity.

Bruno Baguette wrote:

Currently, I have several PostgreSQL databases, some of them are using
LATIN1 encoding, some of them are using UTF-8 encoding.

In order to have theses two encoding, we had to install two PostgreSQL
server on two different ports. One is for LATIN1 databases and one is
for UTF-8 databases. (I known there is a workaround which allows to mix
several databases encoding them on a same PostgreSQL server, by
specifying "C" locale to initdb).

I've heard some rumors on freenode stating that PostgreSQL 8.4. will
allow to have several databases encoding. Did I understand right ?

You can already do that, just use

CREATE DATABASE dbname ENCODING=anyencoding

Yours,
Laurenz Albe

Bruno Baguette-3 wrote:

Hello !

Currently, I have several PostgreSQL databases, some of them are using
LATIN1 encoding, some of them are using UTF-8 encoding.

In order to have theses two encoding, we had to install two PostgreSQL
server on two different ports. One is for LATIN1 databases and one is
for UTF-8 databases. (I known there is a workaround which allows to mix
several databases encoding them on a same PostgreSQL server, by
specifying "C" locale to initdb).

I've heard some rumors on freenode stating that PostgreSQL 8.4. will
allow to have several databases encoding. Did I understand right ?

Thanks in advance for any informations about that feature !

Regards,

--
Bruno Baguette - bruno.baguette@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

1 ACTUALIZAR LOS PAQUETES DEL SISTEMA OPERATIVO
apt-get update
apt-get upgrade
apt-get dist-upgrade
CONFIGURACIÓN DEL IDIOMA EN EL SERVER
1. INSTALAR EL SIGUIENTE PAQUETE

aptitude install utillinux-locale
locale-gen es_CO
aptitude search language | grep sp
aptitude install language-pack-eo-base
locale-gen es
dpkg-reconfigure locales
2. Ediatar la línea /etc/default/locale la cual debe decir lo
siguiente en la primera linea

LANG="es_CO.ISO-8859-1"

Editar la línea /etc/enviroment la cual debe decir lo siguiente despues de
la segunda linea

LANGUAGE="es_CO:es_ES:es:en_US:en"
LANG="es_CO.ISO-8859-1"
LC_CTYPE="es_CO.ISO-8859-1"
LC_MESSAGES="es_CO.ISO-8859-1"
LC_COLLATE="es_CO.ISO-8859-1"
LC_ALL="es_CO.ISO-8859-1"

ejecutar el comando “dpkg-reconfigure locales”

3. reinice el servidor

4. instale nuevamente postgres para que coja el idioma y la codificación
configurada, si no desintala y vuelve a instalar postgres los cambios de
codificación no surgen efecto

+++++++++++++++++ PARA DESINSTALAR POSTGRES

1. miramos que paquete estan instalados con el comando:

aptitude search postgres | grep 8.4

2 desinatalamos con el comando
aptitude purge postgresql-8.4
aptitude purge postgresql-contrib-8.4

++++++++++++++INSTALACION DE POSTGRES++++++++++++++

1. miarqmos que paquete estan disponibles para esta versión de postgres con
el sig comando.

aptitude search postgres | grep 8.4

debe mostrar algo asi como esto:

p postgresql-8.4 - object-relational SQL database,
version 8.
p postgresql-client-8.4 - front-end programs for PostgreSQL 8.4
p postgresql-contrib-8.4 - additional facilities for PostgreSQL
p postgresql-doc-8.4 - documentation for the PostgreSQL
database
p postgresql-plperl-8.4 - PL/Perl procedural language for
PostgreSQL
p postgresql-plpython-8.4 - PL/Python procedural language for
PostgreS
p postgresql-pltcl-8.4 - PL/Tcl procedural language for
PostgreSQL
p postgresql-server-dev-8.4 - development files for PostgreSQL 8.4
serve

2 instalamos los siguientes paquetes con el comando:

aptitude install postgresql-8.4
aptitude install postgresql-contrib-8.4
aptitude install postgresql-server-dev-8.4
aptitude install postgresql-doc-8.4

cuando cree la base de datos esta se crea automáticamente con codificación
LATIN1 la cual es compatible con la bd de capigono.

Siga este manual paso a paso el cual está comprobado que funciona.

COPYRIGHT @ ING. WILLDIMAN MIRA HERNANDEZ

--
View this message in context: http://old.nabble.com/Will-PostgreSQL-8.4-allow-having-different-encoding-databases-on-a-same-PostgreSQL-server---tp22969538p26304056.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.