1

I have a Postgres 9.3 database which, by mistake, has been set to:

enter image description here

but I need it to be:

enter image description here

Since the Encoding doesn't change, it is safe to dump the DB and restore it later (see here) to a database with the new Collation / Character type?

Community
  • 1
  • 1
Teejay
  • 7,210
  • 10
  • 45
  • 76

1 Answers1

4

Perfectly safe -- the collation is just telling Postgres which set of rules to apply when sorting text.

You can even set it dynamically on a query basis in the order by clause, and should be able to alter it without needing to dump the database.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • Thanks for your answer. Anyway it seems it couldn't be changed "on the fly", as per the answer I linked. In fact, on pgAdmin those fields are greyed out. – Teejay Dec 11 '14 at 16:35
  • That question relates to encoding based on its url. The syntax to change the collation on an existing table is `ALTER [ COLUMN ] column [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]`, as [documented here](http://www.postgresql.org/docs/current/static/sql-altertable.html). – Denis de Bernardy Dec 11 '14 at 19:22
  • I just did the dump-reload method, since I needed to change the stnadard collation of the entire database. – Teejay Dec 12 '14 at 09:40