H2 Database - Reorder columns using SQL

Question

I have a H2 database with 16 million entries and no primary key. I successfully added an auto-incrementing primary key using the following statements:

ALTER TABLE
    PUBLIC.ADDRESSES ADD ID BIGINT AUTO_INCREMENT;
ALTER TABLE
    PUBLIC.ADDRESSES ADD PRIMARY KEY (ID)

Now the problem is, that the column order is STREET, HOUSENUMBER, ..., ID, but I would like ID to be the first column of the table. It looks like there is a corresponding ALTER TABLE statement MySQL (see here), but I'm unable to adapt it to H2.

Long story short: How can I change the column order to ID, STREET, HOUSENUMBER ...? Is there a solution similar to:

ALTER TABLE "ADDRESSES" MODIFY COLUMN "ID" BEFORE "STREET";

Any help is kindly appreciated.

There's no standard way to do it, and in some products your only choice is to create a new table and copy the data across. It's not in the standard because, as much as possible, you shouldn't rely on the columns having any particular order. Can't speak to whether h2 has any facilities that will help you here though. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Apr 2, 2014 at 13:29
Indeed this is merely an aesthetic problem when performing SELECT * FROM queries (I don't rely on column order in any case). Thanks for the tip regarding copying the table. — Matt
– Matt, Commented Apr 2, 2014 at 13:48
Maybe you could run multiple alter table statements? First, rename the column, then add a new column with the right name at the right position (alter table add supports positioning), and finally dropping the old column. Or use rename table and then create table ... as select. — Thomas Mueller
– Thomas Mueller, Commented Apr 3, 2014 at 9:56
@ThomasMueller Thanks, I tested both solutions and they worked perfectly (IMHO the CREATE TABLE AS SELECT ... is more elegant than copying and renaming columns). If you converted your comment into an answer I'd be glad to accept it as an answer. — Matt
– Matt, Commented Apr 7, 2014 at 7:06

Thomas Mueller · Accepted Answer · 2014-04-07 07:10:40Z

3

H2 does not currently support re-ordering columns. You would need to run multiple statements:

First, rename the column, then add a new column with the right name at the right position (alter table add supports positioning), and finally drop the old column.
Or, probably more elegant, use rename table and then create table ... as select.

answered Apr 7, 2014 at 7:10

Thomas Mueller

50.4k16 gold badges123 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

H2 Database - Reorder columns using SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related