How to set the database Lc_collate, Lc_ctype, ENCODING, TEMPLATE

Label

PostgreSQL, create DATABASE, collate, CType, pg_encoding, Pg_encoding_to_char ()

Background

PostgreSQL instances support the creation of multiple databases, when you create a database, you can specify a template library, and set different character sets, localized collate, currency, and other attributes for each database.

The following example describes how to use the CREATE database.

First, CREATE database syntax

Command:     CREATE DATABASE  Description: create a new database  Syntax:  CREATE DATABASE name      [ [ WITH ] [ OWNER [=] user_name ]             [ TEMPLATE [=] template ]             [ ENCODING [=] encoding ]             [ LC_COLLATE [=] lc_collate ]             [ LC_CTYPE [=] lc_ctype ]             [ TABLESPACE [=] tablespace_name ]             [ CONNECTION LIMIT [=] connlimit ] ]  

Clone Template Library (create database from specified template) example

Use the test database as a template to clone a database named test01.

test=> create database test01 with template test;  CREATE DATABASE  

If you do not specify a template, the default template is Template1.

Attention

Create a database from the specified template library, except for the current connection, no other user can connect to the corresponding template library.

If another user is connected to the test library, an error like this may be reported

test=> create database test01 with template test;  ERROR:  source database "test" is being accessed by other users  DETAIL:  There is 1 other session using the database.  

Iii. when creating a database, how to specify which encoding Encodingpostgresql support

Users can refer to the official PostgreSQL document, with the corresponding character set support list

Https://www.postgresql.org/docs/9.6/static/multibyte.html

Server=yes indicates that the character set supports the CREATE database. Otherwise only supported as a client character set.

Name	Description	Language	Server?	Bytes/char	Aliases
BIG5	Big Five	Traditional Chinese	No	1-2	WIN950, Windows950
Euc_cn	Extended UNIX CODE-CN	Simplified Chinese	Yes	1-3	-
Euc_jp	Extended UNIX CODE-JP	Japanese	Yes	1-3	-
euc_jis_2004	Extended UNIX code-jp, JIS X 0213	Japanese	Yes	1-3	-
Euc_kr	Extended UNIX Code-kr	Korean	Yes	1-3	-
Euc_tw	Extended UNIX CODE-TW	Traditional Chinese, Taiwanese	Yes	1-3	-
GB18030	National standard	Chinese	No	1-4	-
GBK	Extended National Standard	Simplified Chinese	No	1-2	WIN936, Windows936
Iso_8859_5	ISO 8859-5, ECMA 113	Latin/cyrillic	Yes	1	-
Iso_8859_6	ISO 8859-6, ECMA 114	Latin/arabic	Yes	1	-
Iso_8859_7	ISO 8859-7, ECMA 118	Latin/greek	Yes	1	-
Iso_8859_8	ISO 8859-8, ECMA 121	Latin/hebrew	Yes	1	-
Johab	Johab	Korean (Hangul)	No	1-3	-
Koi8r	Koi8-r	Cyrillic (Russian)	Yes	1	KOI8
koi8u	Koi8-u	Cyrillic (Ukrainian)	Yes	1	-
LATIN1	ISO 8859-1, ECMA 94	Western European	Yes	1	ISO88591
LATIN2	ISO 8859-2, ECMA 94	Central European	Yes	1	ISO88592
LATIN3	ISO 8859-3, ECMA 94	South European	Yes	1	ISO88593
LATIN4	ISO 8859-4, ECMA 94	North European	Yes	1	ISO88594
LATIN5	ISO 8859-9, ECMA 128	Turkish	Yes	1	ISO88599
LATIN6	ISO 8859-10, ECMA 144	Nordic	Yes	1	ISO885910
LATIN7	ISO 8859-13	Baltic	Yes	1	ISO885913
LATIN8	ISO 8859-14	Celtic	Yes	1	ISO885914
LATIN9	ISO 8859-15	LATIN1 with Euro and accents	Yes	1	ISO885915
LATIN10	ISO 8859-16, Asro SR 14111	Romanian	Yes	1	ISO885916
Mule_internal	Mule Internal Code	Multilingual Emacs	Yes	1-4	-
SJIS	Shift JIS	Japanese	No	1-2	Mskanji, ShiftJIS, WIN932, Windows932
shift_jis_2004	Shift JIS, JIS X 0213	Japanese	No	1-2	-
Sql_ascii	Unspecified (see text)	Any	Yes	1	-
UHC	Unified Hangul Code	Korean	No	1-2	WIN949, Windows949
UTF8	Unicode, 8-bit	All	Yes	1-4	Unicode
WIN866	Windows CP866	Cyrillic	Yes	1	Alt
WIN874	Windows CP874	Thai	Yes	1	-
WIN1250	Windows CP1250	Central European	Yes	1	-
WIN1251	Windows CP1251	Cyrillic	Yes	1	WIN
WIN1252	Windows CP1252	Western European	Yes	1	-
WIN1253	Windows CP1253	Greek	Yes	1	-
WIN1254	Windows CP1254	Turkish	Yes	1	-
WIN1255	Windows CP1255	Hebrew	Yes	1	-
WIN1256	Windows CP1256	Arabic	Yes	1	-
WIN1257	Windows CP1257	Baltic	Yes	1	-
WIN1258	Windows CP1258	Vietnamese	Yes	1	ABC, TCVN, TCVN5712, Vscii

Create a database instance of the specified encoding

Create a database with a UTF-8 character set

test=> create database test02 with encoding ‘UTF-8‘;  CREATE DATABASE  

Attention

1. The specified character set must be a superset of the template Library character set, or an error will be given.

2. The specified lc_ctype and lc_collate must be compatible with the target character set.

Example, Template1 is the default template library, and its character set is UTF8.

test=> \l template1                                    List of databases     Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges     -----------+----------+----------+-------------+-------------+-----------------------   template1 | xxxxxxxx | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/xxxxxxxx          +             |          |          |             |             | xxxxxxxx=CTc/xxxxxxxx  (1 row)  

Create a database with a EUC_CN character set

Error 1,EUC_CN the character set is incompatible with the lc_collate,lc_ctype of the template library.

test=> create database test03 with encoding ‘EUC_CN‘;  ERROR:  encoding "EUC_CN" does not match locale "zh_CN.UTF-8"  DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".  

Error 2,EUC_CN character set is incompatible with the character set of the template Library UTF-8.

test=> create database test03 with encoding ‘EUC_CN‘ lc_collate=‘C‘ lc_ctype=‘C‘;  ERROR:  new encoding (EUC_CN) is incompatible with the encoding of the template database (UTF8)  HINT:  Use the same encoding as in the template database, or use template0 as template.  

Iv. How to obtain the Lc_collate supported by the character set, lc_ctype information

Use the following SQL to query the system table Pg_collation get the lc_collate and Lc_ctype supported by the character set.

When encoding is empty, it means that the collation supports all character sets.

Test=> Select Pg_encoding_to_char (collencoding) as Encoding,collname,collcollate,collctype from Pg_collation;       encoding |      Collname |       Collcollate | Collctype------------+-----------------------+-----------------------+-----------------------|                       Default |               | | C | C | C | POSIX | POSIX | POSIX UTF8 | AA_DJ | Aa_dj.utf8 | Aa_dj.utf8 LATIN1 | AA_DJ | AA_DJ | AA_DJ LATIN1 | aa_dj.iso88591 | aa_dj.iso88591 | aa_dj.iso88591 UTF8 | Aa_dj.utf8 | Aa_dj.utf8 | Aa_dj.utf8 UTF8 | Aa_er | Aa_er | Aa_er UTF8 | Aa_er.utf8 | Aa_er.utf8 |   Aa_er.utf8 ..... EUC_CN | ZH_CN | ZH_CN | Zh_cn  UTF8 | ZH_CN | Zh_cn.utf8 | Zh_cn.utf8 EUC_CN | zh_cn.gb2312 | zh_cn.gb2312 | zh_cn.gb2312 UTF8 | Zh_cn.utf8 | Zh_cn.utf8 | Zh_cn.utf8 UTF8 | ZH_HK | Zh_hk.utf8 | Zh_hk.utf8 UTF8 | Zh_hk.utf8 | Zh_hk.utf8 | Zh_hk.utf8 EUC_CN | Zh_sg | Zh_sg | Zh_sg UTF8 | Zh_sg | Zh_sg.utf8 | Zh_sg.utf8 EUC_CN | zh_sg.gb2312 | zh_sg.gb2312 | zh_sg.gb2312 UTF8 | Zh_sg.utf8 | Zh_sg.utf8 | Zh_sg.utf8 EUC_TW | ZH_TW | zh_tw.euctw | ZH_TW.EUCTW UTF8 | ZH_TW | Zh_tw.utf8 | Zh_tw.utf8 EUC_TW | zh_tw.euctw | zh_tw.euctw | ZH_TW.EUCTW UTF8 | Zh_tw.utf8 | Zh_tw.utf8 | Zh_tw.utf8 UTF8 |                 Zu_za| Zu_za.utf8 | Zu_za.utf8 LATIN1 | Zu_za | Zu_za | Zu_za LATIN1 | zu_za.iso88591 | zu_za.iso88591 | zu_za.iso88591 UTF8 | Zu_za.utf8 | Zu_za.utf8 |   Zu_za.utf8 (869 rows)

V. Specify lc_collate when creating a database, lc_ctype example

Create a database, Lc_collate, LC_CTYPE, respectively, Zh_cn.utf8

test=> create database test05 with encoding ‘UTF-8‘ template template0 lc_collate=‘zh_CN.utf8‘ lc_ctype=‘zh_CN.utf8‘;  CREATE DATABASE  

Attention

If the specified lc_collate, LC_CTYPE is incompatible with the collate,ctype of the template Library, an error will be given.

test=> create database test04 with encoding ‘UTF-8‘ lc_collate=‘zh_CN.utf8‘ lc_ctype=‘zh_CN.utf8‘;  ERROR:  new collation (zh_CN.utf8) is incompatible with the collation of the template database (zh_CN.UTF-8)  HINT:  Use the same collation as in the template database, or use template0 as template.  

Workaround 1, use compatible collate and CType.

test=> create database test04 with encoding ‘UTF-8‘ lc_collate=‘zh_CN.UTF-8‘ lc_ctype=‘zh_CN.UTF-8‘;  CREATE DATABASE  

Workaround 2, use TEMPLATE0 as the template Library.

test=> create database test05 with encoding ‘UTF-8‘ template template0 lc_collate=‘zh_CN.utf8‘ lc_ctype=‘zh_CN.utf8‘;  CREATE DATABASE  

Vi. How to modify the Collate,ctype of an existing database

It is not possible to modify the syntax of the ALTER DATABASE directly, so that users can create new databases, export them, and import them in a way.

Example

1. Create a new database, specify targets collate and CType

2. Export the data from the source database using Pg_dump or other client tool logic

3. Use Pg_restore or other client tools to import the second-step export data into a new database

How to set the database Lc_collate, Lc_ctype, ENCODING, TEMPLATE