0

I need to change column collation from default to "C.utf8".

This is the statement I am using:

ALTER TABLE <table_name>
ALTER COLUMN <column_name> SET DATA TYPE VARCHAR COLLATE "C.utf8";

The issue is that SET DATA TYPE is causing errors as there are views and triggers that rely on that specific column. I would rather not have to drop and recreate views and triggers and touch a lot of additional code-base. Is there a syntax for updating collation without requiring SET DATA TYPE?

Postgres version: 14.10

1 Answer 1

0

There is no safe way to do that. Modifying the catalog (and thereby corrupting any index on the column) might look appealing, but I deem that a foolhardy risk, since the proper solution is so simple: have a script that creates all your views (and is checked into a version control system). Then it is no problem to drop the views, run ALTER TABLE and create them again. You could do that in a single transaction.

2
  • Thank you. That is what we ended up doing. However, we didn't create any indexes on the column whose collation we wanted to change. Also, we use a migration tool, so the views and triggers need to have a change in the file hash for the tool to add them again. It just seems like a lot of unnecessary complexity for a column with no index.
    – kravb
    Feb 2 at 13:22
  • I admit that that is unpleasant, but it avoids inconsistencies. We dislike data corruption so much that we risk annoying users to prevent it. Feb 2 at 13:28

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.