<!-- $PostgreSQL: pgsql/doc/src/sgml/charset.sgml,v 2.93 2009/04/0608:42:52 heikki Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/charset.sgml,v 2.94 2009/05/0616:15:20 tgl Exp $ -->

<chapter id="charset">

<title>Localization</>

<listitem>

<para>

Providing a number of different character sets defined in the

<productname>PostgreSQL</productname> server, including

multiple-byte character sets, to support storing text in all

kinds of languages, and providing character set translation between

client and server.

Providing a number of different character sets to support storing text

in all kinds of languages, and providing character set translation

between client and server.

</para>

</listitem>

</itemizedlist>

names on your system depends on what was provided by the operating

system vendor and what was installed. On most Unix systems, the command

<literal>locale -a</> will provide a list of available locales.

Windows uses more verbose names, such as <literal>German_Germany</>

or <literal>Swedish_Sweden.1252</>.

Windows uses more verboselocalenames, such as <literal>German_Germany</>

or <literal>Swedish_Sweden.1252</>, but the principles are the same.

</para>

<para>

fixed when the database is created. You can use different settings

for different databases, but once a database is created, you cannot

change them for that database anymore. <literal>LC_COLLATE</literal>

and <literal>LC_CTYPE</literal> arethose categories. They affect

and <literal>LC_CTYPE</literal> arethese categories. They affect

the sort order of indexes, so they must be kept fixed, or indexes on

text columns will become corrupt. The default values for these

categories are determined when <command>initdb</command> is run, and

For a given locale category, say the collation, the following

environment variables are consulted in this order until one is

found to be set: <envar>LC_ALL</envar>, <envar>LC_COLLATE</envar>

(the variable corresponding to the respective category),

(orthe variable corresponding to the respective category),

<envar>LANG</envar>. If none of these environment variables are

set then the locale defaults to <literal>C</literal>.

</para>

<para>

To enable messages to be translated to the user's preferred language,

<acronym>NLS</acronym> must have been enabled at build time. This

choice is independent of the other locale support.

<acronym>NLS</acronym> must have been selected at build time

(<literal>configure --enable-nls</>). All other locale support is

built in automatically.

</para>

</sect2>

<envar>LC_COLLATE</> locale settings. For <literal>C</> or

<literal>POSIX</> locale, any character set is allowed, but for other

locales there is only one character set that will work correctly.

(On Windows, however, UTF-8 encoding can be used with any locale.)

</para>

<sect2 id="multibyte-charset-supported">

CREATE DATABASE korean WITH ENCODING 'EUC_KR' LC_COLLATE='ko_KR.euckr' LC_CTYPE='ko_KR.euckr' TEMPLATE=template0;

</programlisting>

Notice that the above commands specify copying the <literal>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

<xref linkend="manage-ag-templatedbs">.

</para>

<para>

The encoding for a database is stored in the system catalog

<literal>pg_database</literal>. You can see it by using the

<option>-l</option> option or the <command>\l</command> command

<para>

On most modern operating systems, <productname>PostgreSQL</productname>

can determine which character set is implied by an <envar>LC_CTYPE</>

setting, and it will enforce that only thecorrect database encoding is

setting, and it will enforce that only thematching database encoding is

used. On older systems it is your responsibility to ensure that you use

the encoding expected by the locale you have selected. A mistake in

this area is likely to lead to strange misbehavior of locale-dependent

<listitem>

<para>

The web site of the Unicode Consortium

The web site of the Unicode Consortium.

</para>

</listitem>

</varlistentry>

<!-- $PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.57 2007/11/0815:21:03 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.58 2009/05/06 16:15:20 tgl Exp $ -->

<chapter id="managing-databases">

<title>Managing Databases</title>

<literal>template1</>. This is particularly handy when restoring a

<literal>pg_dump</> dump: the dump script should be restored in a

virgin database to ensure that one recreates the correct contents

of the dumped database, without any conflicts with additions that

can now be present in <literal>template1</>.

of the dumped database, without any conflicts with objects that

might have been added to <literal>template1</> later on.

</para>

<para>

Another common reason for copying <literal>template0</> instead

of <literal>template1</> is that new encoding and locale settings

can be specified when copying <literal>template0</>, whereas a copy

of <literal>template1</> must use the same settings it does.

This is because <literal>template1</> might contain encoding-specific

or locale-specific data, while <literal>template0</> is known not to.

</para>

<para>

<literal>datallowconn</literal>. <literal>datistemplate</literal>

can be set to indicate that a database is intended as a template for

<command>CREATE DATABASE</>. If this flag is set, the database can be

cloned by

any user with <literal>CREATEDB</> privileges; if it is not set, only superusers

and the owner of the database can clone it.

cloned by any user with <literal>CREATEDB</> privileges; if it is not set,

only superusers and the owner of the database can clone it.

If <literal>datallowconn</literal> is false, then no new connections

to that database will be allowed (but existing sessions are not killed

simply by setting the flag false). The <literal>template0</literal>

<title>Destroying a Database</title>

<para>

Databases are destroyed with the command

Databases are destroyed with the command

<xref linkend="sql-dropdatabase" endterm="sql-dropdatabase-title">:<indexterm><primary>DROP DATABASE</></>

<synopsis>

DROP DATABASE <replaceable>name</>;

</synopsis>

Only the owner of the database, or

a superuser, can drop a database. Dropping a database removes all objects

that were

that were

contained within the database. The destruction of a database cannot

be undone.

</para>

<para>

Tables, indexes, and entire databases can be assigned to

particular tablespaces. To do so, a user with the <literal>CREATE</>

privilege on a given tablespace must pass the tablespace name as a

parameter to the relevant command. For example, the following creates

privilege on a given tablespace must pass the tablespace name as a

parameter to the relevant command. For example, the following creates

a table in the tablespace <literal>space1</>:

<programlisting>

CREATE TABLE foo(i int) TABLESPACE space1;

update the <structname>pg_tablespace</> catalog to show the new

locations. (If you do not, <literal>pg_dump</> will continue to show

the old tablespace locations.)

</para>

</para>

</sect1>

</chapter>

<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/create_database.sgml,v 1.51 2009/04/0608:42:52 heikki Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/create_database.sgml,v 1.52 2009/05/0616:15:21 tgl Exp $

PostgreSQL documentation

-->

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="parameter">collate</replaceable></term>

<term><replaceable class="parameter">lc_collate</replaceable></term>

<listitem>

<para>

Collation order (<literal>LC_COLLATE</>) to use in the new database.

This affects the sort order applied to strings, e.g in queries with

This affects the sort order applied to strings, e.g. in queries with

ORDER BY, as well as the order used in indexes on text columns.

The default is to use the collation order of the template database.

See below for additional restrictions.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable class="parameter">ctype</replaceable></term>

<term><replaceable class="parameter">lc_ctype</replaceable></term>

<listitem>

<para>

Character classification (<literal>LC_CTYPE</>) to use in the new

<para>

The character set encoding specified for the new database must be

compatible with the chosenLC_COLLATE and LC_CTYPE settings.

If <envar>LC_CTYPE</> is <literal>C</> (or equivalently

compatible with the chosenlocale settings (<literal>LC_COLLATE</> and

<literal>LC_CTYPE</>). If the locale is <literal>C</> (or equivalently

<literal>POSIX</>), then all encodings are allowed, but for other

locale settings there is only one encoding that will work properly.

(On Windows, however, UTF-8 encoding can be used with any locale.)

<command>CREATE DATABASE</> will allow superusers to specify

<literal>SQL_ASCII</> encoding regardless of the localesetting,

<literal>SQL_ASCII</> encoding regardless of the localesettings,

but this choice is deprecated and may result in misbehavior of

character-string functions if data that is not encoding-compatible

with the locale is stored in the database.

</para>

<para>

The <literal>LC_COLLATE</> and <literal>LC_CTYPE</> settings must match

those of the template database, except when template0 is used as

template. This is because <literal>LC_COLLATE</> and <literal>LC_CTYPE</>

affects the ordering in indexes, so that any indexes copied from the

template database would be invalid in the new database with different

settings. <literal>template0</literal>, however, is known to not

contain any indexes that would be affected.

The encoding and locale settings must match those of the template database,

except when <literal>template0</> is used as template. This is because

other databases might contain data that does not match the specified

encoding, or might contain indexes whose sort ordering is affected by

<literal>LC_COLLATE</> and <literal>LC_CTYPE</>. Copying such data would

result in a database that is corrupt according to the new settings.

<literal>template0</literal>, however, is known to not contain any data or

indexes that would be affected.

</para>

<para>

</para>

<para>

To create a database <literal>music</> which supports the ISO-8859-1

To create a database <literal>music</> which supports the ISO-8859-1

character set:

<programlisting>

CREATE DATABASE music ENCODING 'LATIN1';

CREATE DATABASE music ENCODING 'LATIN1' TEMPLATE template0;

</programlisting>

In this example, the <literal>TEMPLATE template0</> clause would only

be required if <literal>template1</>'s encoding is not ISO-8859-1.

Note that changing encoding might require selecting new

<literal>LC_COLLATE</> and <literal>LC_CTYPE</> settings as well.

</para>

</refsect1>

(encoding==PG_SQL_ASCII&&superuser())))

ereport(ERROR,

(errmsg("encoding %s does not match locale %s",

(errcode(ERRCODE_INVALID_PARAMETER_VALUE),

errmsg("encoding %s does not match locale %s",

pg_encoding_to_char(encoding),

dbctype),

errdetail("The chosen LC_CTYPE setting requires encoding %s.",

(encoding==PG_SQL_ASCII&&superuser())))

ereport(ERROR,

(errmsg("encoding %s does not match locale %s",

(errcode(ERRCODE_INVALID_PARAMETER_VALUE),

errmsg("encoding %s does not match locale %s",

pg_encoding_to_char(encoding),

dbcollate),

errdetail("The chosen LC_COLLATE setting requires encoding %s.",

pg_encoding_to_char(collate_encoding))));

if (strcmp(dbtemplate,"template0")!=0)

{

if (encoding!=src_encoding)

ereport(ERROR,

(errcode(ERRCODE_INVALID_PARAMETER_VALUE),

errmsg("new encoding (%s) is incompatible with the encoding of the template database (%s)",

pg_encoding_to_char(encoding),

pg_encoding_to_char(src_encoding)),

errhint("Use the same encoding as in the template database, or use template0 as template.")));

if (strcmp(dbcollate,src_collate)!=0)

ereport(ERROR,

(errmsg("new collation is incompatible with the collation of the template database (%s)",src_collate),

(errcode(ERRCODE_INVALID_PARAMETER_VALUE),

errmsg("new collation (%s) is incompatible with the collation of the template database (%s)",

dbcollate,src_collate),

errhint("Use the same collation as in the template database, or use template0 as template.")));

if (strcmp(dbctype,src_ctype)!=0)

ereport(ERROR,

(errmsg("new LC_CTYPE is incompatible with LC_CTYPE of the template database (%s)",src_ctype),

(errcode(ERRCODE_INVALID_PARAMETER_VALUE),

errmsg("new LC_CTYPE (%s) is incompatible with the LC_CTYPE of the template database (%s)",

dbctype,src_ctype),

errhint("Use the same LC_CTYPE as in the template database, or use template0 as template.")));

}

continue;

ereport(ERROR,

(errmsg("some relations of database \"%s\" are already in tablespace \"%s\"",

(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),

errmsg("some relations of database \"%s\" are already in tablespace \"%s\"",

dbname,tblspcname),

errhint("You must move them back to the database's default tablespace before using this command.")));

}

$PostgreSQL: pgsql/src/test/mb/README,v 1.3 2008/03/21 13:23:29 momjian Exp $

$PostgreSQL: pgsql/src/test/mb/README,v 1.4 2009/05/06 16:15:21 tgl Exp $

README for multibyte regression test

1998/7/22

This directory contains a set of tests for multibyte supporting

extentions for PostgreSQL. To run the test, simply type:

% mbregress.sh

%shmbregress.sh

ifecho'\c'| grep -s c>/dev/null2>&1

dropdb utf8

createdb -E UTF8 utf8

createdb -T template0 -l C -E UTF8 utf8

PSQL="psql -n -e -q"

tests="euc_jp sjis euc_kr euc_cn euc_tw big5 utf8 mule_internal"

unset PGCLIENTENCODING

dropdb$i>/dev/null2>&1

createdb -E`echo$i| tr'abcdefghijklmnopqrstuvwxyz''ABCDEFGHIJKLMNOPQRSTUVWXYZ'`$i>/dev/null

createdb -T template0 -l C -E`echo$i| tr'abcdefghijklmnopqrstuvwxyz''ABCDEFGHIJKLMNOPQRSTUVWXYZ'`$i>/dev/null

$PSQL$i< sql/${i}.sql> results/${i}.out2>&1