CREATE DATABASE 命令的具体使用方法

  • A+
所属分类:PostgreSQL
高性能企业级服务器首台5折

PostgreSQL 实例支持创建多个数据库。创建数据库时,您可以指定模板库,并为每个数据库设置不同的字符集、本地化 collate、货币等属性。

本文将通过使用示例来介绍如何通过

1
CREATE DATABASE

命令来设置模板库、字符集、LC_COLLATE 及 LC_CTYPE 的信息。

CREATE DATABASE 命令的语法

  1. 1
    <span class="typ">Command</span><span class="pun">:</span><span class="pln">     <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">DATABASE</span>  </span>
  2. 1
    <span class="typ">Description</span><span class="pun">:</span><span class="pln"> <span class="hljs-built_in">create</span> <span class="hljs-keyword">a</span> </span><span class="kwd"><span class="hljs-built_in">new</span></span><span class="pln"> database  </span>
  3. 1
    <span class="typ"><span class="hljs-symbol">Syntax</span></span><span class="pun"><span class="hljs-symbol">:</span></span>
  4. 1
    <span class="pln"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">DATABASE</span> <span class="hljs-keyword">name</span>  </span>
  5. 1
        <span class="pun">[</span> <span class="pun">[</span><span class="pln"> WITH </span><span class="pun">]</span> <span class="pun">[</span><span class="pln"> OWNER </span><span class="pun">[=]</span><span class="pln"> user_name </span><span class="pun">]</span>
  6. 1
               <span class="pun">[</span><span class="pln"> TEMPLATE </span><span class="pun">[=]</span> <span class="kwd">template</span> <span class="pun">]</span>
  7. 1
               <span class="pun">[</span><span class="pln"> ENCODING </span><span class="pun">[=]</span><span class="pln"> encoding </span><span class="pun">]</span>
  8. 1
               <span class="pun">[</span><span class="pln"> LC_COLLATE </span><span class="pun">[=]</span><span class="pln"> lc_collate </span><span class="pun">]</span>
  9. 1
               <span class="pun">[</span><span class="pln"> LC_CTYPE </span><span class="pun">[=]</span><span class="pln"> lc_ctype </span><span class="pun">]</span>
  10. 1
               <span class="pun">[</span><span class="pln"> TABLESPACE </span><span class="pun">[=]</span><span class="pln"> tablespace_name </span><span class="pun">]</span>
  11. 1
               <span class="pun">[</span><span class="pln"><span class="hljs-built_in"> CONNECTION </span>LIMIT </span><span class="pun">[=]</span><span class="pln"> connlimit </span><span class="pun">]</span> <span class="pun">]</span>

克隆模板库

克隆模板库是指从指定模板创建数据库。如下示例将介绍如何通过

1
CREATE DATABASE

命令从指定模板创建数据库。

使用示例

以 test 数据库为模板,克隆一个名为 test01 的数据库,其命令如下所示:

  1. 1
    <span class="pln">test</span><span class="pun">=&gt;</span><span class="pln"> <span class="hljs-keyword">create</span> <span class="hljs-keyword">database</span> test01 </span><span class="kwd"><span class="hljs-keyword">with</span></span> <span class="kwd">template</span><span class="pln"> test</span><span class="pun">;</span>
  2. 1
    <span class="pln"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">DATABASE</span></span>

注意:

  • 若不指定模板,则默认的模板为 template1。
  • 从指定模板库创建数据库时,除了当前连接,不能有其他用户连在对应的模板库上面。例如,若有其他用户连在 test 数据库上,可能会报类似如下的错误:
    1. 1
      <span class="pln">  test</span><span class="pun">=&gt;</span><span class="pln"> <span class="hljs-keyword">create</span> <span class="hljs-keyword">database</span> test01 </span><span class="kwd"><span class="hljs-keyword">with</span></span> <span class="kwd">template</span><span class="pln"> test</span><span class="pun">;</span>
    2. 1
      <span class="pln">  ERROR</span><span class="pun">:</span><span class="pln">  source database </span><span class="str"><span class="hljs-string">"test"</span></span> <span class="kwd">is</span><span class="pln"> being accessed </span><span class="kwd">by</span><span class="pln"> other<span class="hljs-built_in"> users </span> </span>
    3. 1
      <span class="pln">  DETAIL</span><span class="pun">:</span>  <span class="typ">There</span> <span class="kwd"><span class="hljs-keyword">is</span></span> <span class="lit"><span class="hljs-number">1</span></span><span class="pln"> other session </span><span class="kwd"><span class="hljs-keyword">using</span></span><span class="pln"> the database</span><span class="pun">.</span>

指定字符集类型

创建数据库时,您可以指定 encoding。如下示例将介绍如何通过

1
CREATE DATABASE

命令创建指定字符集类型的数据库。

PostgreSQL 支持的字符集类型

您可以通过 PostgreSQL 的官方文档或下表查看对应的字符集支持列表,Server=Yes 表示该字符集支持用于

1
CREATE DATABASE

命令,Server=No 表示只支持作为客户端字符集。

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

使用示例

创建一个字符集为 UTF-8 的数据库,其命令如下所示:

  1. 1
    <span class="pln">test</span><span class="pun">=&gt;</span><span class="pln"> <span class="hljs-keyword">create</span> <span class="hljs-keyword">database</span> test02 </span><span class="kwd"><span class="hljs-keyword">with</span></span><span class="pln"> encoding </span><span class="str"><span class="hljs-string">'UTF-8'</span></span><span class="pun">;</span>
  2. 1
    <span class="pln"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">DATABASE</span></span>

注意:

  • 指定的字符集必须是模板库字符集的超集,否则会报错。
  • 指定的 LC_COLLATE 和 LC_CTYPE 必须与目标字符集兼容,否则会报错。
  • 报错示例:template1 是默认模板库,它的字符集为 UTF8,如下所示。
    1. 1
      <span class="pln"><span class="hljs-meta">test</span></span><span class="pun"><span class="hljs-meta">=&gt;</span></span><span class="pln"><span class="clojure"> \l template<span class="hljs-number">1</span>  </span></span>
    2. 1
                                      <span class="typ"><span class="hljs-built_in">List</span></span><span class="pln"> <span class="hljs-keyword">of</span> databases  </span>
    3. 1
       <span class="typ">Name</span>    <span class="pun">|</span>  <span class="typ"><span class="hljs-type">Owner</span></span>   <span class="pun">|</span> <span class="typ"><span class="hljs-type">Encoding</span></span> <span class="pun">|</span>   <span class="typ"><span class="hljs-type">Collate</span></span>   <span class="pun">|</span>    <span class="typ"><span class="hljs-type">Ctype</span></span>    <span class="pun">|</span>   <span class="typ"><span class="hljs-type">Access</span></span><span class="pln"> privileges     </span>
    4. 1
      <span class="pun"><span class="hljs-params">-----------</span>+<span class="hljs-params">----------</span>+<span class="hljs-params">----------</span>+<span class="hljs-params">-------------</span>+<span class="hljs-params">-------------</span>+<span class="hljs-params">-----------------------</span></span>
    5. 1
      <span class="pln">template1 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">xxxxxxxx</span> </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">UTF8</span>     </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span></span><span class="pun">.</span><span class="pln">UTF</span><span class="pun"><span class="hljs-number">-</span></span><span class="lit"><span class="hljs-number">8</span></span> <span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span></span><span class="pun">.</span><span class="pln">UTF</span><span class="pun"><span class="hljs-number">-</span></span><span class="lit"><span class="hljs-number">8</span></span> <span class="pun">|</span> <span class="pun"><span class="hljs-type">=</span></span><span class="pln"><span class="hljs-type">c</span></span><span class="pun">/</span><span class="pln">xxxxxxxx          </span><span class="pun">+</span>
    6. 1
               <span class="pun">|</span>          <span class="pun">|</span>          <span class="pun">|</span>             <span class="pun">|</span>             <span class="pun">|</span><span class="pln"><span class="hljs-string"> xxxxxxxx</span></span><span class="pun"><span class="hljs-string">=</span></span><span class="typ"><span class="hljs-string">CTc</span></span><span class="pun"><span class="hljs-string">/</span></span><span class="pln"><span class="hljs-string">xxxxxxxx  </span></span>
    7. 1
      <span class="pun">(</span><span class="lit"><span class="hljs-name">1</span></span><span class="pln"> row</span><span class="pun">)</span>

    当使用上述 template1 作为模板创建一个字符集为 EUC_CN 的数据库时,会出现如下错误:

    • EUC_CN 字符集与模板库的 LC_COLLATE 和 LC_CTYPE 不兼容。
      1. 1
        <span class="pln">test</span><span class="pun">=&gt;</span><span class="pln"> <span class="hljs-keyword">create</span> <span class="hljs-keyword">database</span> test03 </span><span class="kwd"><span class="hljs-keyword">with</span></span><span class="pln"> encoding </span><span class="str"><span class="hljs-string">'EUC_CN'</span></span><span class="pun">;</span>
      2. 1
        <span class="pln"><span class="hljs-keyword">ERROR</span></span><span class="pun"><span class="hljs-keyword">:</span></span><span class="pln">  encoding </span><span class="str">"EUC_CN"</span><span class="pln"> does </span><span class="kwd">not</span><span class="pln"> match locale </span><span class="str">"zh_CN.UTF<span class="hljs-string">-8</span>"</span>
      3. 1
        <span class="pln"><span class="hljs-symbol">DETAIL</span></span><span class="pun"><span class="hljs-symbol">:</span></span>  <span class="typ">The</span><span class="pln"> chosen LC_CTYPE setting requires encoding </span><span class="str"><span class="hljs-string">"UTF8"</span></span><span class="pun">.</span>
    • EUC_CN 字符集与模板库的字符集 UTF-8 不兼容。
      1. 1
        <span class="pln">test</span><span class="pun">=&gt;</span><span class="pln"> create database test03 </span><span class="kwd"><span class="hljs-keyword">with</span></span><span class="pln"> encoding </span><span class="str"><span class="hljs-string">'EUC_CN'</span></span><span class="pln"> lc_collate</span><span class="pun">=</span><span class="str"><span class="hljs-string">'C'</span></span><span class="pln"> lc_ctype</span><span class="pun">=</span><span class="str"><span class="hljs-string">'C'</span></span><span class="pun">;</span>
      2. 1
        <span class="pln"><span class="hljs-keyword">ERROR</span></span><span class="pun"><span class="hljs-keyword">:</span></span>  <span class="kwd">new</span><span class="pln"> encoding </span><span class="pun">(</span><span class="pln">EUC_CN</span><span class="pun">)</span> <span class="kwd">is</span><span class="pln"> incompatible </span><span class="kwd">with</span><span class="pln"> the encoding of the </span><span class="kwd">template</span><span class="pln"> database </span><span class="pun">(</span><span class="pln">UTF8</span><span class="pun">)</span>
      3. 1
        <span class="pln">HINT</span><span class="pun">:</span>  <span class="typ"><span class="hljs-keyword">Use</span></span><span class="pln"> the same <span class="hljs-keyword">encoding</span> </span><span class="kwd"><span class="hljs-keyword">as</span></span> <span class="kwd"><span class="hljs-keyword">in</span></span><span class="pln"> the </span><span class="kwd"><span class="hljs-keyword">template</span></span><span class="pln"> <span class="hljs-keyword">database</span></span><span class="pun">,</span> <span class="kwd"><span class="hljs-keyword">or</span></span> <span class="kwd"><span class="hljs-keyword">use</span></span><span class="pln"> template0 </span><span class="kwd"><span class="hljs-keyword">as</span></span> <span class="kwd">template</span><span class="pun">.</span>
  • 解决方法示例:使用 template0 作为模板库,即可解决上述报错的问题。
    1. 1
      <span class="pln">  <span class="hljs-keyword">create</span> <span class="hljs-keyword">database</span> test03 </span><span class="kwd"><span class="hljs-keyword">with</span></span><span class="pln"> <span class="hljs-keyword">encoding</span> </span><span class="str"><span class="hljs-string">'EUC_CN'</span></span> <span class="kwd"><span class="hljs-keyword">template</span></span><span class="pln"> template0</span><span class="pun">;</span>

设置 LC_COLLATE 和 LC_CTYPE 的信息

本示例将介绍如何查询字符集支持的 LC_COLLATE 和 LC_CTYPE、如何通过

1
CREATE DATABASE

命令指定 LC_COLLATE 和 LC_CTYPE 以及如何修改已有数据库的 LC_COLLATE 和 LC_CTYPE。

查询字符集支持的 LC_COLLATE 和 LC_CTYPE 信息

您可以使用如下 SQL 查询系统表 pg_collation,来获取字符集支持的LC_COLLATE 和 LC_CTYPE 信息。

  1. 1
    <span class="pln"><span class="hljs-meta">test</span></span><span class="pun"><span class="hljs-meta">=&gt;</span></span> <span class="kwd"><span class="clojure">select</span></span><span class="pln"><span class="clojure"> pg_encoding_to_char</span></span><span class="pun"><span class="clojure">(</span></span><span class="pln"><span class="clojure"><span class="hljs-name">collencoding</span></span></span><span class="pun"><span class="clojure">)</span></span> <span class="kwd"><span class="clojure">as</span></span><span class="pln"><span class="clojure"> encoding</span></span><span class="pun"><span class="clojure">,</span></span><span class="pln"><span class="clojure">collname</span></span><span class="pun"><span class="clojure">,</span></span><span class="pln"><span class="clojure">collcollate</span></span><span class="pun"><span class="clojure">,</span></span><span class="pln"><span class="clojure">collctype </span></span><span class="kwd"><span class="clojure">from</span></span><span class="pln"><span class="clojure"> pg_collation </span></span><span class="pun"><span class="clojure"><span class="hljs-comment">;</span></span></span>

返回结果如下所示,encoding 为空时,表示这个 collation 支持所有的字符集。

  1. 1
    <span class="pln">  encoding  </span><span class="pun">|</span><span class="pln">       <span class="hljs-type">collname</span>        </span><span class="pun">|</span><span class="pln">      <span class="hljs-type">collcollate</span>      </span><span class="pun">|</span><span class="pln">       <span class="hljs-type">collctype</span>         </span>
  2. 1
    <span class="pun"><span class="hljs-params">------------</span>+<span class="hljs-params">-----------------------</span>+<span class="hljs-params">-----------------------</span>+<span class="hljs-params">-----------------------</span></span>
  3. 1
                <span class="pun">|</span> <span class="kwd"><span class="hljs-built_in">default</span></span>               <span class="pun">|</span>                       <span class="pun">|</span>
  4. 1
                <span class="pun">|</span><span class="pln"> <span class="hljs-type">C</span>                     </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">C</span>                     </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">C</span>  </span>
  5. 1
                <span class="pun">|</span><span class="pln"> <span class="hljs-type">POSIX</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">POSIX</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">POSIX</span>  </span>
  6. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_DJ</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_DJ</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_DJ</span></span><span class="pun">.</span><span class="pln">utf8  </span>
  7. 1
    <span class="pln"> LATIN1     </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_DJ</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_DJ</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_DJ</span>  </span>
  8. 1
    <span class="pln"> LATI<span class="hljs-symbol">N1</span>     </span><span class="pun">|</span><span class="pln"> aa_DJ</span><span class="pun">.</span><span class="pln">is<span class="hljs-meta">o88591</span>        </span><span class="pun">|</span><span class="pln"> aa_DJ</span><span class="pun">.</span><span class="pln">is<span class="hljs-meta">o88591</span>        </span><span class="pun">|</span><span class="pln"> aa_DJ</span><span class="pun">.</span><span class="pln">is<span class="hljs-meta">o88591</span>  </span>
  9. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_DJ</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_DJ</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_DJ</span></span><span class="pun">.</span><span class="pln">utf8  </span>
  10. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_ER</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_ER</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_ER</span>  </span>
  11. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_ER</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_ER</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-literal">a</span><span class="hljs-built_in">a_ER</span></span><span class="pun">.</span><span class="pln">utf8  </span>
  12. 1
    <span class="pun">.......</span>
  13. 1
    <span class="pln"> EUC_CN     </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span>  </span>
  14. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span></span><span class="pun">.</span><span class="pln">utf8  </span>
  15. 1
    <span class="pln"> EUC_CN     </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span></span><span class="pun">.</span><span class="pln">gb2312          </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span></span><span class="pun">.</span><span class="pln">gb2312          </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span></span><span class="pun">.</span><span class="pln">gb2312  </span>
  16. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_CN</span></span><span class="pun">.</span><span class="pln">utf8  </span>
  17. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_HK</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_HK</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_HK</span></span><span class="pun">.</span><span class="pln">utf8  </span>
  18. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_HK</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_HK</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_HK</span></span><span class="pun">.</span><span class="pln">utf8  </span>
  19. 1
    <span class="pln"> EUC_CN     </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_SG</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_SG</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_SG</span>  </span>
  20. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_SG</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_SG</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_SG</span></span><span class="pun">.</span><span class="pln">utf8  </span>
  21. 1
    <span class="pln"> EUC_CN     </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_SG</span></span><span class="pun">.</span><span class="pln">gb2312          </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_SG</span></span><span class="pun">.</span><span class="pln">gb2312          </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_SG</span></span><span class="pun">.</span><span class="pln">gb2312  </span>
  22. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_SG</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_SG</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_SG</span></span><span class="pun">.</span><span class="pln">utf8  </span>
  23. 1
    <span class="pln"> EUC_TW     </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_TW</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_TW</span></span><span class="pun">.</span><span class="pln">euctw           </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_TW</span></span><span class="pun">.</span><span class="pln">euctw  </span>
  24. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_TW</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_TW</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_TW</span></span><span class="pun">.</span><span class="pln">utf8  </span>
  25. 1
    <span class="pln"> EUC_TW     </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_TW</span></span><span class="pun">.</span><span class="pln">euctw           </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_TW</span></span><span class="pun">.</span><span class="pln">euctw           </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_TW</span></span><span class="pun">.</span><span class="pln">euctw  </span>
  26. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_TW</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_TW</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zh_TW</span></span><span class="pun">.</span><span class="pln">utf8  </span>
  27. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zu_ZA</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zu_ZA</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zu_ZA</span></span><span class="pun">.</span><span class="pln">utf8  </span>
  28. 1
    <span class="pln"> LATIN1     </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zu_ZA</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zu_ZA</span>                 </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zu_ZA</span>  </span>
  29. 1
    <span class="pln"> LATI<span class="hljs-symbol">N1</span>     </span><span class="pun">|</span><span class="pln"> zu_ZA</span><span class="pun">.</span><span class="pln">is<span class="hljs-meta">o88591</span>        </span><span class="pun">|</span><span class="pln"> zu_ZA</span><span class="pun">.</span><span class="pln">is<span class="hljs-meta">o88591</span>        </span><span class="pun">|</span><span class="pln"> zu_ZA</span><span class="pun">.</span><span class="pln">is<span class="hljs-meta">o88591</span>  </span>
  30. 1
    <span class="pln"> UTF8       </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zu_ZA</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zu_ZA</span></span><span class="pun">.</span><span class="pln">utf8            </span><span class="pun">|</span><span class="pln"> <span class="hljs-type">zu_ZA</span></span><span class="pun">.</span><span class="pln">utf8  </span>
  31. 1
    <span class="pun">(</span><span class="lit"><span class="hljs-name">869</span></span><span class="pln"> rows</span><span class="pun">)</span>

创建数据库时指定 LC_COLLATE 和 LC_CTYPE

使用示例

创建一个 LC_COLLATE 和 LC_CTYPE 分别为 zh_CN.utf8 的数据库,其命令如下所示:

  1. 1
    <span class="pln">test</span><span class="pun">=&gt;</span><span class="pln"> create database test05 </span><span class="kwd"><span class="hljs-keyword">with</span></span><span class="pln"> encoding </span><span class="str"><span class="hljs-symbol">'UTF</span>-<span class="hljs-number">8</span><span class="hljs-string">'</span></span> <span class="kwd"><span class="hljs-string">template</span></span><span class="pln"><span class="hljs-string"> template0 lc_collate</span></span><span class="pun"><span class="hljs-string">=</span></span><span class="str"><span class="hljs-string">'</span>zh_CN.utf8'</span><span class="pln"> lc_ctype</span><span class="pun">=</span><span class="str"><span class="hljs-symbol">'zh_CN</span>.utf8'</span><span class="pun">;</span>
  2. 1
    <span class="pln"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">DATABASE</span></span>

注意:

若指定的 LC_COLLATE 和 LC_CTYPE 与模板库的 LC_COLLATE 和 LC_CTYPE 不兼容,会出现如下错误:

  1. 1
    <span class="pln">test</span><span class="pun">=&gt;</span><span class="pln"> create database test04 </span><span class="kwd"><span class="hljs-keyword">with</span></span><span class="pln"> encoding </span><span class="str"><span class="hljs-symbol">'UTF</span>-<span class="hljs-number">8</span><span class="hljs-string">'</span></span><span class="pln"><span class="hljs-string"> lc_collate</span></span><span class="pun"><span class="hljs-string">=</span></span><span class="str"><span class="hljs-string">'</span>zh_CN.utf8'</span><span class="pln"> lc_ctype</span><span class="pun">=</span><span class="str"><span class="hljs-symbol">'zh_CN</span>.utf8'</span><span class="pun">;</span>
  2. 1
    <span class="pln"><span class="hljs-keyword">ERROR</span></span><span class="pun"><span class="hljs-keyword">:</span></span>  <span class="kwd">new</span><span class="pln"> collation </span><span class="pun">(</span><span class="pln">zh_CN</span><span class="pun">.</span><span class="pln">utf8</span><span class="pun">)</span> <span class="kwd">is</span><span class="pln"> incompatible </span><span class="kwd">with</span><span class="pln"> the collation of the </span><span class="kwd">template</span><span class="pln"> database </span><span class="pun">(</span><span class="pln">zh_CN</span><span class="pun">.</span><span class="pln">UTF</span><span class="pun"><span class="hljs-string">-</span></span><span class="lit"><span class="hljs-string">8</span></span><span class="pun">)</span>
  3. 1
    <span class="pln">HINT</span><span class="pun">:</span>  <span class="typ"><span class="hljs-keyword">Use</span></span><span class="pln"> the same <span class="hljs-keyword">collation</span> </span><span class="kwd"><span class="hljs-keyword">as</span></span> <span class="kwd"><span class="hljs-keyword">in</span></span><span class="pln"> the </span><span class="kwd"><span class="hljs-keyword">template</span></span><span class="pln"> <span class="hljs-keyword">database</span></span><span class="pun">,</span> <span class="kwd"><span class="hljs-keyword">or</span></span> <span class="kwd"><span class="hljs-keyword">use</span></span><span class="pln"> template0 </span><span class="kwd"><span class="hljs-keyword">as</span></span> <span class="kwd">template</span><span class="pun">.</span>

出现上述错误时,有如下两种解决方法:

  • 使用兼容的 LC_COLLATE 和 LC_CTYPE,其命令如下所示:
    1. 1
      <span class="pln">test</span><span class="pun">=&gt;</span><span class="pln"> create database test04 </span><span class="kwd"><span class="hljs-keyword">with</span></span><span class="pln"> encoding </span><span class="str"><span class="hljs-string">'UTF-8'</span></span><span class="pln"> lc_collate</span><span class="pun">=</span><span class="str"><span class="hljs-string">'zh_CN.UTF-8'</span></span><span class="pln"> lc_ctype</span><span class="pun">=</span><span class="str"><span class="hljs-string">'zh_CN.UTF-8'</span></span><span class="pun">;</span>
    2. 1
      <span class="pln"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">DATABASE</span></span>
  • 使用 template0 作为模板库,其命令如下所示:
    1. 1
      <span class="pln">test</span><span class="pun">=&gt;</span><span class="pln"> create database test05 </span><span class="kwd"><span class="hljs-keyword">with</span></span><span class="pln"> encoding </span><span class="str"><span class="hljs-symbol">'UTF</span>-<span class="hljs-number">8</span><span class="hljs-string">'</span></span> <span class="kwd"><span class="hljs-string">template</span></span><span class="pln"><span class="hljs-string"> template0 lc_collate</span></span><span class="pun"><span class="hljs-string">=</span></span><span class="str"><span class="hljs-string">'</span>zh_CN.utf8'</span><span class="pln"> lc_ctype</span><span class="pun">=</span><span class="str"><span class="hljs-symbol">'zh_CN</span>.utf8'</span><span class="pun">;</span>
    2. 1
      <span class="pln"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">DATABASE</span></span>

修改已有数据库的 LC_COLLATE 和 LC_CTYPE 信息

目前,您无法直接通过 

1
alter database

 命令修改已有数据库的 LC_COLLATE 和 LC_CTYPE 信息,但可以通过创建新的数据库,然后导出再导入数据的方式进行修改。

操作步骤

  1. 创建新数据库,指定目标 LC_COLLATE 和 LC_CTYPE。
  2. 使用 pg_dump 或其它客户端工具逻辑导出源数据库的数据。
  3. 使用 pg_restore 或其它客户端工具,将第 2 步导出的数据导入新数据库。

参考文档

PostgreSQL 9.6.2 Documentation — CREATE DATABASE

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: