How to determine the collation of a table in PostgreSQL?

I want to script a check of the collations used on my tables in PostgreSQL, but googling for Postgresql detect collation is not working well for me, and the documentation is not making this an easy search.

Can anyone tell me how I would check this?

To check for non-default collations on columns, you can use the following query:

select table_schema, 
       table_name, 
       column_name,
       collation_name
from information_schema.columns
where collation_name is not null
order by table_schema,
         table_name,
         ordinal_position;

To find the collation of the database, you need to query pg_database:

select datname, 
       datcollate
from pg_database;

Here are the relevant pages of the PostgreSQL manual:

• http://www.postgresql.org/docs/current/static/infoschema-columns.html

• http://www.postgresql.org/docs/current/static/infoschema-collations.html

• http://www.postgresql.org/docs/current/static/catalog-pg-database.html

StackExchange Database Administrators Q#29943, answer score: 50