3

Such as change from C to utf8?

I tried this but seems not allowed.

postgres=# ALTER DATABASE mydb SET "Collate" To 'en_US.UTF-8';
ERROR:  unrecognized configuration parameter "Collate"

1 Answer 1

5

No, that is not possible.

The reason is that there may be data structures in the database that depend on the database's collation: indexes over string data types. If you'd be able to change the collation on the fly, all these indexes would implicitly become corrupted.

You might say that is OK, let's just rebuild all these indexes. Well, if you are ready to do that, there is a way: pg_dump the database, create a new database with the desired collation and restore the dump into the new database. Sure, that will take longer than just rebuilding the string indexes, but the expense is in the same order of magnitude, and this is a safe way.

You may be tempted to meddle with the pg_database system catalog manually, but I can only discourage that: as the saying is, if you do that and things break, you get to keep both pieces.

6
  • In fact collation is a plain part of the datatype when the datatype is enrolled in the "literal" familly. Changing collation must result to change the datatype.
    – SQLpro
    Sep 15, 2020 at 10:47
  • @SQLpro Sorry, that is to profound for a lowly hacker like me. What is the literal family, and how do you enroll a data type in it? pg_attribute stores 0 for the default collation. Sep 15, 2020 at 10:49
  • @@@@@@@@ Laurenz, According to SQL standards, litteral family datatype are CHAR, VARCHAR, NCHAR, NVARCHAR. PG use only CHAR and VARCHAR mixing ASCII encoding and UNICODE. When the default collation is zero, I suppose (I have no PG at the moment) the collation is inhereted from the database collation.
    – SQLpro
    Sep 15, 2020 at 11:07
  • So what has all that to do with the question or my answer? Sep 15, 2020 at 11:18
  • The asker seems to consider that the collation is an execution parameter and not a parameter of the datatype. It is just to clarify your answer...
    – SQLpro
    Sep 15, 2020 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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