collations

The collations view provides information about collations for each character set. This view is read-only. All users have the read permission on this view.

**Table 1** information_schema.collations columns
Name	Type	Description
COLLATION_NAME	varchar(32)	Name of the collation.
CHARACTER_SET_NAME	varchar(32)	Name of the character set with which the collation is associated.
ID	bigint	Sorting rule ID. For details, see pg_collation.id.
IS_DEFAULT	varchar(3)	Specifies whether the collation is the default for its character set.
IS_COMPILED	varchar(3)	Specifies whether the character set is compiled into the server.
SORTLEN	bigint	This is not supported in the current version. The default value is null.

The control behavior of the GUC parameter m_format_dev_version is forward compatible. When m_format_dev_version is set to s2, the latest function can be used. When m_format_dev_version is set to other values, forward compatibility is retained.

If the value of m_format_dev_version is not s2, the view displays only the character set sorting rules related to the character set of the current database.

m_db=# SET m_format_dev_version=s1;
SET
m_db=#
m_db=# SELECT * FROM information_schema.collations;
   COLLATION_NAME   | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN
--------------------+--------------------+-----+------------+-------------+---------
 utf8mb4_0900_ai_ci | UTF8               | 255 |            | Yes         |
 utf8_bin           | UTF8               |  83 |            | Yes         |
 utf8_unicode_ci    | UTF8               | 192 |            | Yes         |
 utf8_general_ci    | UTF8               |  33 |            | Yes         |
 utf8mb4_bin        | UTF8               |  46 |            | Yes         |
 utf8mb4_unicode_ci | UTF8               | 224 |            | Yes         |
 utf8mb4_general_ci | UTF8               |  45 | Yes        | Yes         |
 POSIX              | UTF8               |     |            | Yes         |
 C                  | UTF8               |     |            | Yes         |
 default            | UTF8               |     |            | Yes         |
(10 rows)

If m_format_dev_version is set to s2, you can query all available collations in the view.

m_db=# SET m_format_dev_version=s2;
SET
m_db=# SELECT * FROM information_schema.collations;
   COLLATION_NAME   | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN
--------------------+--------------------+-----+------------+-------------+---------
 binary             | binary             |  63 | Yes        | Yes         |
 gbk_chinese_ci     | gbk                |  28 | Yes        | Yes         |
 gbk_bin            | gbk                |  87 |            | Yes         |
 utf8mb4_general_ci | utf8mb4            |  45 | Yes        | Yes         |
 utf8mb4_unicode_ci | utf8mb4            | 224 |            | Yes         |
 utf8mb4_bin        | utf8mb4            |  46 |            | Yes         |
 utf8_general_ci    | utf8               |  33 |            | Yes         |
 utf8_unicode_ci    | utf8               | 192 |            | Yes         |
 utf8_bin           | utf8               |  83 |            | Yes         |
 utf8mb4_0900_ai_ci | utf8mb4            | 255 |            | Yes         |
 gb18030_chinese_ci | gb18030            | 248 | Yes        | Yes         |
 gb18030_bin        | gb18030            | 249 |            | Yes         |
 latin1_swedish_ci  | latin1             |   8 | Yes        | Yes         |
 latin1_bin         | latin1             |  47 |            | Yes         |
(14 rows)