Is there a alter grammer to change columns' ORDINAL_POSITION

[王乔]

like this below in MySQL

ALTER TABLE tableName.api_key_usage CHANGE account_id account_id bigint(20) NOT NULL AFTER `TIMESTAMP`;

[Evgenij Ryazanov]

Hello.

No, there is no such feature in H2 (and in the SQL Standard too).

You can add a new column at position that you need, copy data to it with UPDATE, recreate indexes and constraints (if old column was referenced by something), drop old column, and finally rename a new one.

[areichel]

Or alternatively, create a backup of that table, drop the columns starting from the end, add the new column, add the other columns (as NULLABLE), update the the columns from the backup table, modify the columns to not null, create indexes.

Please see the example below.

It might have the advantage, that references to other tables and statistics stay intact (when the relevant index/key columns are in front of the table).

INSERT INTO risk.version_info
VALUES      (0
             , 99
             , 7
             , SYSDATE
             , NULL);

---------------------------------------------------------------------------------------------------------------
-- Changes

create table risk.tmp_collateral
as select *
from risk.collateral;


drop index RISK.COLLATERAL_IDX1;
drop index RISK.COLLATERAL_IDX2;
drop index RISK.COLLATERAL_IDX3;
drop index RISK.COLLATERAL_IDX4;

alter table risk.collateral
drop column update_timestamp;

alter table risk.collateral
drop column id_status;

alter table risk.collateral
drop column id_organization_unit;

alter table risk.collateral
drop column id_user_editor;

alter table risk.collateral
drop column block_auto_update_flag;

alter table risk.collateral
add column coordinates VARCHAR(40);

alter table risk.collateral
add column block_auto_update_flag VARCHAR(1) NULL;

alter table risk.collateral
add column id_user_editor         NUMBER(10) NULL;

alter table risk.collateral
add column id_organization_unit   NUMBER(10) NULL;

alter table risk.collateral
add column id_status              VARCHAR(1) NULL;

alter table risk.collateral
add column update_timestamp       TIMESTAMP NULL;

update risk.collateral a
set (block_auto_update_flag 
     , id_user_editor
     , id_organization_unit
     , id_status, update_timestamp) = (select block_auto_update_flag 
                                              , id_user_editor
                                              , id_organization_unit
                                              , id_status
                                              , update_timestamp
                                       from risk.tmp_collateral
                                       where id_collateral_ref = a.id_collateral_ref);

alter table risk.collateral
alter column block_auto_update_flag VARCHAR(1) NOT NULL;

alter table risk.collateral
alter column id_user_editor         NUMBER(10) NOT NULL;

alter table risk.collateral
alter column id_organization_unit   NUMBER(10) NOT NULL;

alter table risk.collateral
alter column id_status              VARCHAR(1) NOT NULL;

create index risk.collateral_idx1 on risk.collateral(id_collateral_ref, id_collateral, id_status);
create index risk.collateral_idx2 on risk.collateral(id_collateral_type, id_status);
create index risk.collateral_idx3 on risk.collateral(ID_STATUS,ID_COLLATERAL_REF);
create index RISK.collateral_idx4 on RISK.COLLATERAL(ID_COLLATERAL,ID_STATUS);

drop table risk.tmp_collateral;

---------------------------------------------------------------------------------------------------------------
-- VIEWS
UPDATE risk.version_info
SET    installation_end = SYSDATE
WHERE  installation_end IS NULL;