Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 15 down vote accepted

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

select table_schema, 
from information_schema.columns
where collation_name is not null
order by table_schema,

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

select datname, 
from pg_database;

Here is the link to the relevant parts of the manual

share|improve this answer
Hmm... and how can I tell what the default collation is? – Jeremy Holovacs Dec 6 '12 at 21:52
@JeremyHolovacs: see my edit. – a_horse_with_no_name Dec 6 '12 at 21:56
Thanks, that helped a lot. – Jeremy Holovacs Dec 6 '12 at 22:05

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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