3

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.

Community
  • 1
  • 1
Matt
  • 17,290
  • 7
  • 57
  • 71
  • 1
    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 Apr 02 '14 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 Apr 02 '14 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 Apr 03 '14 at 09: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 Apr 07 '14 at 07:06

1 Answers1

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.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132