pg_collation

A system table describing the available collations

pg_collation is a system catalogue table describing the available collations.

pg_collation was added in PostgreSQL 9.1.

psql

  • \dO lists the available user-defined collations
  • \dOS lists all collations, including system ones

Notes

The integer value in the collencoding column can be converted to a human-readable form using the pg_encoding_to_char() function.

Definition by PostgreSQL version

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         | 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)
    

Documentation: 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)
    

Documentation: 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)
    

Documentation: 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)
    

Documentation: 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)
    

Documentation: 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)
    

Documentation: 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)
    

Documentation: 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)
    

Documentation: 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)
    

Documentation: 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)
    

Documentation: 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)
    

Documentation: 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)
    

Documentation: pg_collation

Change history

Examples

Example contents of a pg_collation record:

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         | 

Use pg_encoding_to_char() to decode the value in the collencoding column:

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

Categories

System catalogue