On Tue, Jun 27, 2017 at 1:39 PM, Tomasz Tyrakowski t.tyrakow...@sol-system.pl [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
