pg_collation

描述可用排序规则的系统表

pg_collation 是一个描述可用排序规则的系统目录表。

pg_collation 在 PostgreSQL 9.1 中添加。

psql

\dO 列出可用的用户定义排序规则
\dOS 列出所有排序规则，包括系统自带的

备注

可以使用 pg_encoding_to_char() 函数将 collencoding 列中的整数值转换为人类可读的格式。

按 PostgreSQL 版本定义

pg_collation (PostgreSQL 19)

                Table "pg_catalog.pg_collation"
       Column        |  Type   | Collation | Nullable | Default 
---------------------+---------+-----------+----------+---------
 oid                 | oid     |           | not null | 
 collname            | name    |           | not null | 
 collnamespace       | oid     |           | not null | 
 collowner           | oid     |           | not null | 
 collprovider        | "char"  |           | not null | 
 collisdeterministic | boolean |           | not null | 
 collencoding        | integer |           | not null | 
 collcollate         | text    | C         |          | 
 collctype           | text    | C         |          | 
 colllocale          | text    | C         |          | 
 collicurules        | text    | C         |          | 
 collversion         | text    | C         |          | 
Indexes:
    "pg_collation_oid_index" PRIMARY KEY, btree (oid)
    "pg_collation_name_enc_nsp_index" UNIQUE CONSTRAINT, btree (collname, collencoding, collnamespace)

文档: pg_collation

pg_collation (PostgreSQL 18)

                Table "pg_catalog.pg_collation"
       Column        |  Type   | Collation | Nullable | Default 
---------------------+---------+-----------+----------+---------
 oid                 | oid     |           | not null | 
 collname            | name    |           | not null | 
 collnamespace       | oid     |           | not null | 
 collowner           | oid     |           | not null | 
 collprovider        | "char"  |           | not null | 
 collisdeterministic | boolean |           | not null | 
 collencoding        | integer |           | not null | 
 collcollate         | text    | C         |          | 
 collctype           | text    | C         |          | 
 colllocale          | text    | C         |          | 
 collicurules        | text    | C         |          | 
 collversion         | text    | C         |          | 
Indexes:
    "pg_collation_oid_index" PRIMARY KEY, btree (oid)
    "pg_collation_name_enc_nsp_index" UNIQUE CONSTRAINT, btree (collname, collencoding, collnamespace)

文档: pg_collation

pg_collation (PostgreSQL 17)

                Table "pg_catalog.pg_collation"
       Column        |  Type   | Collation | Nullable | Default 
---------------------+---------+-----------+----------+---------
 oid                 | oid     |           | not null | 
 collname            | name    |           | not null | 
 collnamespace       | oid     |           | not null | 
 collowner           | oid     |           | not null | 
 collprovider        | "char"  |           | not null | 
 collisdeterministic | boolean |           | not null | 
 collencoding        | integer |           | not null | 
 collcollate         | text    | C         |          | 
 collctype           | text    | C         |          | 
 colllocale          | text    | C         |          | 
 collicurules        | text    | C         |          | 
 collversion         | text    | C         |          | 
Indexes:
    "pg_collation_oid_index" PRIMARY KEY, btree (oid)
    "pg_collation_name_enc_nsp_index" UNIQUE CONSTRAINT, btree (collname, collencoding, collnamespace)

文档: pg_collation

pg_collation (PostgreSQL 16)

                Table "pg_catalog.pg_collation"
       Column        |  Type   | Collation | Nullable | Default 
---------------------+---------+-----------+----------+---------
 oid                 | oid     |           | not null | 
 collname            | name    |           | not null | 
 collnamespace       | oid     |           | not null | 
 collowner           | oid     |           | not null | 
 collprovider        | "char"  |           | not null | 
 collisdeterministic | boolean |           | not null | 
 collencoding        | integer |           | not null | 
 collcollate         | text    | C         |          | 
 collctype           | text    | C         |          | 
 colliculocale       | text    | C         |          | 
 collicurules        | text    | C         |          | 
 collversion         | text    | C         |          | 
Indexes:
    "pg_collation_oid_index" PRIMARY KEY, btree (oid)
    "pg_collation_name_enc_nsp_index" UNIQUE CONSTRAINT, btree (collname, collencoding, collnamespace)

文档: pg_collation

pg_collation (PostgreSQL 15)

                Table "pg_catalog.pg_collation"
       Column        |  Type   | Collation | Nullable | Default 
---------------------+---------+-----------+----------+---------
 oid                 | oid     |           | not null | 
 collname            | name    |           | not null | 
 collnamespace       | oid     |           | not null | 
 collowner           | oid     |           | not null | 
 collprovider        | "char"  |           | not null | 
 collisdeterministic | boolean |           | not null | 
 collencoding        | integer |           | not null | 
 collcollate         | text    | C         |          | 
 collctype           | text    | C         |          | 
 colliculocale       | text    | C         |          | 
 collversion         | text    | C         |          | 
Indexes:
    "pg_collation_oid_index" PRIMARY KEY, btree (oid)
    "pg_collation_name_enc_nsp_index" UNIQUE CONSTRAINT, btree (collname, collencoding, collnamespace)

文档: pg_collation

pg_collation (PostgreSQL 14)

                Table "pg_catalog.pg_collation"
       Column        |  Type   | Collation | Nullable | Default 
---------------------+---------+-----------+----------+---------
 oid                 | oid     |           | not null | 
 collname            | name    |           | not null | 
 collnamespace       | oid     |           | not null | 
 collowner           | oid     |           | not null | 
 collprovider        | "char"  |           | not null | 
 collisdeterministic | boolean |           | not null | 
 collencoding        | integer |           | not null | 
 collcollate         | name    |           | not null | 
 collctype           | name    |           | not null | 
 collversion         | text    | C         |          | 
Indexes:
    "pg_collation_oid_index" PRIMARY KEY, btree (oid)
    "pg_collation_name_enc_nsp_index" UNIQUE CONSTRAINT, btree (collname, collencoding, collnamespace)

文档: pg_collation

pg_collation (PostgreSQL 13)

                Table "pg_catalog.pg_collation"
       Column        |  Type   | Collation | Nullable | Default 
---------------------+---------+-----------+----------+---------
 oid                 | oid     |           | not null | 
 collname            | name    |           | not null | 
 collnamespace       | oid     |           | not null | 
 collowner           | oid     |           | not null | 
 collprovider        | "char"  |           | not null | 
 collisdeterministic | boolean |           | not null | 
 collencoding        | integer |           | not null | 
 collcollate         | name    |           | not null | 
 collctype           | name    |           | not null | 
 collversion         | text    | C         |          | 
Indexes:
    "pg_collation_name_enc_nsp_index" UNIQUE, btree (collname, collencoding, collnamespace)
    "pg_collation_oid_index" UNIQUE, btree (oid)

文档: pg_collation

pg_collation (PostgreSQL 12)

                Table "pg_catalog.pg_collation"
       Column        |  Type   | Collation | Nullable | Default 
---------------------+---------+-----------+----------+---------
 oid                 | oid     |           | not null | 
 collname            | name    |           | not null | 
 collnamespace       | oid     |           | not null | 
 collowner           | oid     |           | not null | 
 collprovider        | "char"  |           | not null | 
 collisdeterministic | boolean |           | not null | 
 collencoding        | integer |           | not null | 
 collcollate         | name    |           | not null | 
 collctype           | name    |           | not null | 
 collversion         | text    | C         |          | 
Indexes:
    "pg_collation_name_enc_nsp_index" UNIQUE, btree (collname, collencoding, collnamespace)
    "pg_collation_oid_index" UNIQUE, btree (oid)

文档: pg_collation

pg_collation (PostgreSQL 11)

             Table "pg_catalog.pg_collation"
    Column     |  Type   | Collation | Nullable | Default 
---------------+---------+-----------+----------+---------
 collname      | name    |           | not null | 
 collnamespace | oid     |           | not null | 
 collowner     | oid     |           | not null | 
 collprovider  | "char"  |           | not null | 
 collencoding  | integer |           | not null | 
 collcollate   | name    |           | not null | 
 collctype     | name    |           | not null | 
 collversion   | text    |           |          | 
Indexes:
    "pg_collation_name_enc_nsp_index" UNIQUE, btree (collname, collencoding, collnamespace)
    "pg_collation_oid_index" UNIQUE, btree (oid)

文档: pg_collation

pg_collation (PostgreSQL 10)

             Table "pg_catalog.pg_collation"
    Column     |  Type   | Collation | Nullable | Default 
---------------+---------+-----------+----------+---------
 collname      | name    |           | not null | 
 collnamespace | oid     |           | not null | 
 collowner     | oid     |           | not null | 
 collprovider  | "char"  |           | not null | 
 collencoding  | integer |           | not null | 
 collcollate   | name    |           | not null | 
 collctype     | name    |           | not null | 
 collversion   | text    |           |          | 
Indexes:
    "pg_collation_name_enc_nsp_index" UNIQUE, btree (collname, collencoding, collnamespace)
    "pg_collation_oid_index" UNIQUE, btree (oid)

文档: pg_collation

pg_collation (PostgreSQL 9.6)

   Table "pg_catalog.pg_collation"
    Column     |  Type   | Modifiers 
---------------+---------+-----------
 collname      | name    | not null
 collnamespace | oid     | not null
 collowner     | oid     | not null
 collencoding  | integer | not null
 collcollate   | name    | not null
 collctype     | name    | not null
Indexes:
    "pg_collation_name_enc_nsp_index" UNIQUE, btree (collname, collencoding, collnamespace)
    "pg_collation_oid_index" UNIQUE, btree (oid)

文档: pg_collation

pg_collation (PostgreSQL 9.5)

   Table "pg_catalog.pg_collation"
    Column     |  Type   | Modifiers 
---------------+---------+-----------
 collname      | name    | not null
 collnamespace | oid     | not null
 collowner     | oid     | not null
 collencoding  | integer | not null
 collcollate   | name    | not null
 collctype     | name    | not null
Indexes:
    "pg_collation_name_enc_nsp_index" UNIQUE, btree (collname, collencoding, collnamespace)
    "pg_collation_oid_index" UNIQUE, btree (oid)

文档: pg_collation

pg_collation (PostgreSQL 9.4)

   Table "pg_catalog.pg_collation"
    Column     |  Type   | Modifiers 
---------------+---------+-----------
 collname      | name    | not null
 collnamespace | oid     | not null
 collowner     | oid     | not null
 collencoding  | integer | not null
 collcollate   | name    | not null
 collctype     | name    | not null
Indexes:
    "pg_collation_name_enc_nsp_index" UNIQUE, btree (collname, collencoding, collnamespace)
    "pg_collation_oid_index" UNIQUE, btree (oid)

文档: pg_collation

pg_collation (PostgreSQL 9.3)

   Table "pg_catalog.pg_collation"
    Column     |  Type   | Modifiers 
---------------+---------+-----------
 collname      | name    | not null
 collnamespace | oid     | not null
 collowner     | oid     | not null
 collencoding  | integer | not null
 collcollate   | name    | not null
 collctype     | name    | not null
Indexes:
    "pg_collation_name_enc_nsp_index" UNIQUE, btree (collname, collencoding, collnamespace)
    "pg_collation_oid_index" UNIQUE, btree (oid)

文档: pg_collation

pg_collation (PostgreSQL 9.2)

   Table "pg_catalog.pg_collation"
    Column     |  Type   | Modifiers 
---------------+---------+-----------
 collname      | name    | not null
 collnamespace | oid     | not null
 collowner     | oid     | not null
 collencoding  | integer | not null
 collcollate   | name    | not null
 collctype     | name    | not null
Indexes:
    "pg_collation_name_enc_nsp_index" UNIQUE, btree (collname, collencoding, collnamespace)
    "pg_collation_oid_index" UNIQUE, btree (oid)

文档: pg_collation

pg_collation (PostgreSQL 9.1)

   Table "pg_catalog.pg_collation"
    Column     |  Type   | Modifiers 
---------------+---------+-----------
 collname      | name    | not null
 collnamespace | oid     | not null
 collowner     | oid     | not null
 collencoding  | integer | not null
 collcollate   | name    | not null
 collctype     | name    | not null
Indexes:
    "pg_collation_name_enc_nsp_index" UNIQUE, btree (collname, collencoding, collnamespace)
    "pg_collation_oid_index" UNIQUE, btree (oid)

文档: pg_collation

变更历史

PostgreSQL 17
- 列 colliculocale 重命名为 colllocale (提交 f696c0cd)
PostgreSQL 16
- 添加列 collicurules (提交 30a53b79)
PostgreSQL 15
- 添加列 colliculocale (提交 f2553d43)
- 列 collcollate 从 name 更改为 text (提交 54637508)
- 列 collctype 从 name 更改为 text (提交 54637508)
PostgreSQL 12
- 添加列 collisdeterministic (提交 5e1963fb)
PostgreSQL 10
- 添加以下列 (提交 eccfef81)
  - collprovider
  - collversion
PostgreSQL 9.1
- 添加 (提交 414c5a2e)

示例

pg_collation 记录的示例内容

postgres=# SELECT * FROM pg_collation WHERE collname='en_GB.utf8'\gx
Expanded display is on.
-[ RECORD 1 ]-------+-----------
oid                 | 12529
collname            | en_GB.utf8
collnamespace       | 11
collowner           | 10
collprovider        | c
collisdeterministic | t
collencoding        | 6
collcollate         | en_GB.utf8
collctype           | en_GB.utf8
collversion         |

使用 pg_encoding_to_char() 解码 collencoding 列中的值

postgres=# SELECT pg_encoding_to_char(6);
 pg_encoding_to_char 
---------------------
 UTF8
(1 row)

参考资料

PostgreSQL 文档: pg_collation

另请参阅

CREATE COLLATION, ALTER COLLATION, DROPCOLLATION

反馈

在此处提交关于“pg_collation”的任何评论、建议或更正 此处.

版本图表

实用概述

有用链接

咖啡

目录

pg_collation

psql

备注

按 PostgreSQL 版本定义

pg_collation (PostgreSQL 19)

pg_collation (PostgreSQL 18)

pg_collation (PostgreSQL 17)

pg_collation (PostgreSQL 16)

pg_collation (PostgreSQL 15)

pg_collation (PostgreSQL 14)

pg_collation (PostgreSQL 13)

pg_collation (PostgreSQL 12)

pg_collation (PostgreSQL 11)

pg_collation (PostgreSQL 10)

pg_collation (PostgreSQL 9.6)

pg_collation (PostgreSQL 9.5)

pg_collation (PostgreSQL 9.4)

pg_collation (PostgreSQL 9.3)

pg_collation (PostgreSQL 9.2)

pg_collation (PostgreSQL 9.1)

变更历史

示例

参考资料

分类

另请参阅

反馈