Add a non-standard DDL command to change position of a column

[katzyn]

• https://groups.google.com/forum/#!topic/h2-database/vMgLhUXlAaE
• Add synthetic ALTER TABLE t1 MOVE c1, c2, ... TO t2 [ BEFORE x | AFTER x | FIRST ] command jOOQ/jOOQ#9515

@grandinj
@lukaseder
What syntax should be used from your point of view? I don't like the CHANGE syntax from MySQL, we partially support it in MySQL compatibility mode (without possibility to reorder the column), but it is disabled in Regular mode and I don't want to enable it.

The syntax from jOOQ should actually look like

ALTER TABLE tableName MOVE columnName [, ...]
    { { { BEFORE | AFTER } columnName } | FIRST }

[grandinj]

That jOOQ issue is unrelated.

Firebird uses
ALTER TABLE table_name ALTER field_name POSITION new_position

[katzyn]

Oh, I missed “between tables” in the description. My bad.

Anyway, we need to choose something, such command is useful, because the alternative requires many slow (in large tables) changes.

The variant from Firebird is not very good for H2, because H2 expects a data type in that construction where the POSITION is used. We can support it anyway, but maybe there are better alternatives?

[katzyn]

Because H2 uses { { BEFORE | AFTER } columnName } | FIRST in ALTER TABLE tableName ADD command it's reasonable to reuse them for this new command too.

But in the syntax from Firebird 1-based numeric offsets are used, such syntax is not very user-friendly.

[lukaseder]

I had Firebird's syntax in mind, which we also want to support in jOOQ, but indeed, my idea here is to move a column between tables. This is much more sophisticated, and I don't know all the details of this feature yet (or whether it is even a good idea). There needs to be some data migration clause associated with it, and depending on the complexity of possible migrations, it might be better not to abstract over the operation and let users continue to do this manually.

I do think Firebird's feature (with whatever syntax) is very useful though. I'm still surprised so few databases see the logical order of columns in tables as something worth investing in. I find it very useful, to be able to specify that column order.