PostgreSQL 数据库编码

PostgreSQL 数据库编码问题

目录

• PostgreSQL 数据库编码问题
  • 1. 数据库不支持中文，如何使用utf-8编码
    • 解决办法：
      • 方法一：直接修改数据库编码
      • 方法二：修改模板库编码，一劳永逸。
      • 方法三：创建/删除模板库
  • 2. 深入理解
    • 模板库
    • 数据库编码
      • 修改表 pg_database / 修改数据库编码 / 修改模板库编码

1. 数据库不支持中文，如何使用utf-8编码

解决办法：

注意：以下所有代码都应在数据库中执行，即可以输入 ‘\l’ 查看数据库的命令行，其行首有 ’=#‘ (超级用户) 或 ’=>‘ (普通用户)。

方法一：直接修改数据库编码

# 修改 datname 为你的数据库名字，两条命令效果相同，选择一个即可
update pg_database set encoding=6 where datname='postgres';
# 命令2
update pg_database set encoding=pg_char_to_encoding('UTF8') 
where datname='postgres';

方法二：修改模板库编码，一劳永逸。

# 修改模板库template1编码, 同理可修改 template0
update pg_database 
set (encoding, datctype, datcollate)=(6, 'en_US.UTF8','en_US.UTF8') 
where datname = 'template1';

方法三：创建/删除模板库

# 创建模板库，这是一条命令，SQL语法允许命令分行，以分号结束，但回车后，前一行命令不可修改
# 复制前先将第一行的 template1 改成你想要的名字，比如任何简单的名字: td 
create database template1 with 
template template0 
encoding='UTF8' 
lc_ctype='en_US.UTF8' 
lc_collate='en_US.UTF8' 
allow_connections=TRUE 
is_template=True;

# 以后创建数据库指定模板创建即可
create database template mytemplate;

# 也可以一劳永逸，删除原来的 template1, 重新创建即可
# 先将模板库变成普通数据库，使 datistemplate=True 可以将任何普通数据库变成模板库。
update pg_database set datistemplate=False where datname='template1';
# 删除数据库
drop database template1;

2. 深入理解

模板库

PostgreSQL 安装后自带三个库：template0，template1 和 postgres。

template 是模板的意思，template0，template1都是模板库，创建数据库便实质是拷贝模板库，默认的模板库是 template1。两个模板库的唯一区别就是 template1 是可连接的，而 template0 不可连接，就是 \c template 不能进入数据库。这个属性是可以修改的(见下文)。

postgres 是一个普通的数据库，不能作为模板。这个属性也是可以更改的(见下文)，所以任何数据库都能变成模板库，模板库唯一特点就是不能被删除，所以要想删除模板库，需要先将模板库变成普通数据库。创建 postgres 数据库唯一的目的便是方便进入数据库，因为进入数据库的命令psql的默认参数是psql -U 用户名 -d 用户名命名的数据库 ，如psql -U postgres -d postgres， PostgreSQL 默认创建的用户名为 postgres, 所以要创建一个名为postgres的数据库。数据库名是必要参数，如果没有就进不了数据库。

注意：创建数据库必须依赖模板库，所以不要删除了全部的数据库，否则没有模板库，就再也没法创建数据库了。

数据库编码

数据库编码都是可以直接修改的，所有数据库基本参数都存放在表 pg_database 中，超级用户可以在任何数据库中查看它、修改它。

# 查看 pg_database 表结构，
\d pg_database
    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers 
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datcollate    | name      | not null
 datctype      | name      | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 datminmxid    | xid       | not null
 dattablespace | oid       | not null
 datacl        | aclitem[] | 

• 参数说明

  • datname 数据库名字， name类型即字符串，用单引号 ‘’ 括起来，如 ‘template1’。

  • encoding 数据库编码代号，一个整数。utf-8的代号为6，代号也可以通过函数得到：

    update pg_database set encoding=6 where ...
    update pg_database set encoding=pg_char_to_encoding('UTF8') where ...
    

  • datcollate、datctype 分别对应数据库的 Collate 和 Ctype。name字符串，可为 ‘en_US.UTF-8’。模板库的这两个字段必须和encoding对应，否则创建数据库时会报错：

    ERROR:  encoding "UTF8" does not match locale "en_US"
    DETAIL:  The chosen LC_CTYPE setting requires encoding "LATIN1".
    

    • ‘en_US.UTF-8’ 对应编码 UTF8 、 ‘en_US’ 对应编码 LATIN1 。
    • 去了解更多编码：https://www.postgresql.org/docs/12/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED

  • datistemplate 是否为模板库， 布尔值： false/true ，不限大小写: TRUE

  • datallowconn 是否能连接，布尔值。

查看表 pg_database 内容

select * from pg_database;
 datname  | datdba | encoding | datcollate |  datctype  | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace |               datacl                
----------+--------+----------+------------+------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------
postgres  |     10 |        6 | en_US      | en_US      | f             | t            |           -1 |         12416 |          662 |          1 |          1663 | 
template1 |     10 |        6 | en_US.UTF8 | en_US.UTF8 | t             | t            |           -1 |         12416 |          662 |          1 |          1663 | 
template0 |     10 |        6 | en_US.UTF8 | en_US.UTF8 | t             | f            |           -1 |         12416 |          662 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}
(3 rows)

修改表 pg_database / 修改数据库编码 / 修改模板库编码

这三个问题是等同的，都是用命令 update 修改表 pg_database

# 查看 update 命令用法
\h update 
Command:     UPDATE
Description: update rows of a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

这里只解释两种用法：

# 用法一：
update pg_database set datname='template1' where datname='mytemplate';
# 只能更改表的一个字段，例如这句修改数据库的名字，只要将默认的模板删了，就可以把自己的模板设置成默认模板了。

# 用法二：
update pg_database 
set (encoding, datcollate, datctype)=(6, 'en_US.UTF8','en_US.UTF8') 
where datname='template1';
# 更改表的多个字段，比如修改模板库的编码，需要同时修改三个字段。

创建数据库时，也可以自定义某些参数，并不是完全复制模板库，甚至可以直接创建模板库。如下所示：

\h create database
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 ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

• 解释：
  • [] 里左边的名字才是参数名，右边的是说明，但 SQL 语句不区分大小写，使用小写字母更直观，如 create database allow_connections true 。害怕写错单词可以直接复制，使用大写字母。
  • owner 拥有者，template 模板
  • encoding 编码， lc_collate Collate属性， lc_ctype Ctype属性，这三个字段必须对应，设置时应同时设置。
  • allow_connection 允许连接，istemplate 是模板库

如：创建一个模板库

create database mytemplate 
encoding='UTF8' 
lc_ctype='en_US.UTF8' 
lc_collate='en_US.UTF8'
IS_TEMPLATE true
TEMPLATE template0;

敲重点：数据库的三个有关编码的字段 encoding、ctype、collate 必须同时设置，关系要对应，比如 ‘en_US.UTF-8’ 对应的编码为 UTF8，代号6， 对应 ‘en_US’ 对应的编码为 LATIN1，代号8 。 ‘en_US.UTF-8’ 虽为字符串，但一个字也不能错。