In PostgreSQL, we can use the pg_collation catalog to get a list of the available collations. Another way to do it is to use the \dOS command.

Using the first method, we can run the following statement to return a list of available collations in PostgreSQL:

SELECT * 
FROM pg_collation;

These collations are mappings from an SQL name to operating system locale categories.

Given the list of collations is quite long, you can also narrow the results down with a WHERE clause if you have a particular collation in mind:

SELECT * 
FROM pg_collation
WHERE collname like '%NZ%';

Just as with any query, you can also narrow down the number of columns:

SELECT 
	collname, 
	collencoding 
FROM pg_collation
WHERE collname like '%NZ%';

 

On my machine, this results in the following collation list:

collname	collencoding
en_NZ	6
en_NZ.ISO8859-1	8
en_NZ.UTF-8	6
en_NZ.ISO8859-15	16
en_NZ	8
en_NZ	16
en-NZ-x-icu	-1

The \dOS Command

We can also use the \dOS command to return the collations. When using this command, we can use a pattern to narrow the results to just those collations that match the pattern:

\dOS az*

Result:

                                 List of collations
   Schema   |       Name       |  Collate   |   Ctype    | Provider | Deterministic? 
------------+------------------+------------+------------+----------+----------------
 pg_catalog | az-Cyrl-AZ-x-icu | az-Cyrl-AZ | az-Cyrl-AZ | icu      | yes
 pg_catalog | az-Cyrl-x-icu    | az-Cyrl    | az-Cyrl    | icu      | yes
 pg_catalog | az-Latn-AZ-x-icu | az-Latn-AZ | az-Latn-AZ | icu      | yes
 pg_catalog | az-Latn-x-icu    | az-Latn    | az-Latn    | icu      | yes
 pg_catalog | az-x-icu         | az         | az         | icu      | yes
(5 rows)

The S modifier specifies that system objects should be included in the result.

We can also use the + symbol to return more information:

\dOS+ az*

Result:

                                                    List of collations
   Schema   |       Name       |  Collate   |   Ctype    | Provider | Deterministic? |            Description             
------------+------------------+------------+------------+----------+----------------+------------------------------------
 pg_catalog | az-Cyrl-AZ-x-icu | az-Cyrl-AZ | az-Cyrl-AZ | icu      | yes            | Azerbaijani (Cyrillic, Azerbaijan)
 pg_catalog | az-Cyrl-x-icu    | az-Cyrl    | az-Cyrl    | icu      | yes            | Azerbaijani (Cyrillic)
 pg_catalog | az-Latn-AZ-x-icu | az-Latn-AZ | az-Latn-AZ | icu      | yes            | Azerbaijani (Latin, Azerbaijan)
 pg_catalog | az-Latn-x-icu    | az-Latn    | az-Latn    | icu      | yes            | Azerbaijani (Latin)
 pg_catalog | az-x-icu         | az         | az         | icu      | yes            | Azerbaijani
(5 rows)

Note that only collations usable with the current database’s encoding are shown.