How to determine the collation of a table in PostgreSQL?

Question

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?

Answer 1

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

Answer 2

In addition to a_horse_with_no_name's answer, if you want the collation for the database you're currently connected to, you can use the following query:

SELECT datcollate AS collation
FROM pg_database 
WHERE datname = current_database();