Is it problematic to change the collation setting of only text columns without changing the database collation in PostgreSQL?

I am working with a PostgreSQL database and I need to change the collation setting of specific text columns. However, I do not want to alter the collation of the entire database.

The database currently has case-insensitive indexing on integer columns (specifically on ID columns) and there are no indexes on the text columns I want to modify. I am assuming that changing the collation of only the text columns shouldn't cause any issues since there are no indexes on these columns.

My question is: Will changing the collation on the text columns cause any problems with existing data or queries, even though the database's collation is not being changed and no indexes are affected?

Are there any performance considerations or potential issues I should be aware of in this scenario?

The current database collation is set to Turkish_Turkey.1254, as shown by this query:

SELECT datname, datcollate, datctype 
FROM pg_database 
WHERE datname = '';

To change the collation of all text type columns in the database to a case-insensitive collation, I'm running the following PL/pgSQL block:

DO $$
DECLARE
    t record;
BEGIN
    FOR t IN 
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns
        WHERE table_catalog = '' 
          AND data_type = 'text' 
          AND table_schema NOT LIKE 'pg_%'
        ORDER BY table_schema
    LOOP
        EXECUTE format('ALTER TABLE %I.%I ALTER COLUMN %I SET DATA TYPE text COLLATE "tr-x-icu"', 
                       t.table_schema, t.table_name, t.column_name);
    END LOOP;
END
$$;

Solution

It is fine to change the collation of a column. If there were any indexes on the column, the indexes would be rebuilt.

Potential side effects are:

It can change your query results, but that is presumably exactly why you want to change the collation in the first place.
Since different collations are not equally fast when comparing, there could also be a performance impact.
If you change to a non-deterministic collation (for example, a case-insensitive one), LIKE won't work any more for PostgreSQL versions under v18.
Regular expressions are not supported on strings with a non-deterministic collation.

Other than that, I am not aware of any potential problems.