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

Source: Internet
Author: User
Tags posix


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


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.


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


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  

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  

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.


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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.