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;
On Thursday, November 7, 2019 at 12:28:10 PM UTC+7, Evgenij Ryazanov wrote:
>
> 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.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/ee870ce3-c35a-42b9-82e8-f6716d6a0b66%40googlegroups.com.
