On Tue, Jun 27, 2017 at 1:39 PM, Tomasz Tyrakowski t.tyrakowski@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

,

Is it possible to change the collation of a varchar column in place
(without making a temporary column, copying data, dropping the original
column, making it anew and copying the data back)?

Not if you have indexes, a declared primary key, or a declared unique

constraint.  The index keys are a transformation of the value into a format

that produces the desired ordering through binary comparisons.  

 

Would setting
RDB$RELATION_FIELDS.RDB$ COLLATION do the trick (and is it safe to do)?

Not safe, won't work, and will produce very odd results on range queries on any

index.  Or, say, a query that causes Firebird to return rows in index order. 

 

I've got several old production databases which have different
collations on different columns (probably by mistake) and would like to
sort it out.

However messy with inter-column dependencies, the only way to make the

change is to create a new column with the desired collation, copy the data,

drop the old column, recreate the old column with the new collation and 

repopulate it.  

 

Also, does the RDB$RELATION_FIELDS.RDB$ COLLATION somehow depend on the
default character set of the database, or is it universal (i.e. does the
collation code from RDB$RELATION_FIELDS.RDB$ COLLATION always mean the
same, or does a single code mean different collations in databases with
different default character sets)?

The format of the keys depends on the character set - different character sets

have different meanings for some byte values, which collate differently.

Good luck,

Ann