目录

pg_collation

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

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

pg_collation 在PostgreSQL 9.1 中添加。

psql

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

注释

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

按 PostgreSQL 版本定义

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

更改历史记录

示例

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)

分类

排序规则, 系统目录

另请参见

CREATE COLLATION, ALTER COLLATION, DROP COLLATION

反馈

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