17

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?

22

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;

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

select datname, 
       datcollate
from pg_database;

Here is the link to the relevant parts of the manual

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.