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
  • 6,913
  • 10
  • 43
  • 72

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
  • 72,128
  • 12
  • 122
  • 148
  • 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