Adding a column to table with SQL

Question

I have a SQL problem with a h2 database. I want to add a column but always get an error although I strictly stick to the syntax.

Example:

create table t1 (c1 bool,c3 bool);
alter table t1 add column c2 bool after c1;

Creating the table is no problem. But when it comes to the second line a get the following error:

Syntax error in SQL statement "ALTER TABLE T1 ADD COLUMN C2 BOOL AFTER[*] C1 "; SQL statement:
alter table t1 add column c2 bool after c1 [42000-168] 42000/42000 (Help)

What am I doing wrong?

Edit: The same error message is displayed when I omit "column":

alter table t1 add c2 bool after c1;

Then it works but it adds my the new column at the last position which I don't want. — principal-ideal-domain, Jul 09 '14 at 23:21
Try leaving out `after`. I noticed that [someone else](http://dba.stackexchange.com/questions/44348/h2-alter-table-add-column-before-after) has also had problems with that. And documentation (http://www.h2database.com/html/grammar.html#alter_table_add) clearly states that both `column` and `after`should be supported. — Simo Erkinheimo, Jul 09 '14 at 23:24
Looks like H2 doesn't support reordering, [here](http://stackoverflow.com/questions/22812765/h2-database-reorder-columns-using-sql) Thomas Mueller come with some solutions. — David Zhou, Jul 09 '14 at 23:25

score 1 · Answer 1 · answered Jul 09 '14 at 23:12

1

Please remove the column

create table t1 (c1 bool,c3 bool);
alter table t1 add c2 bool after c1;

answered Jul 09 '14 at 23:12

Bhasyakarulu Kottakota

823
1
6
10

Rahul · Answer 2 · 2014-07-09T23:40:06.003

0

Per the posted H2 documentation link the syntax for ALTER is

ALTER TABLE TEST ADD CREATEDATE TIMESTAMP

So from your posted ALTER query as below, only difference it inclusion of column

alter table t1 add column c2 bool after c1;

EDIT:

Per H2 Spec AFTER and BEFORE should work but if it doesn't work then spec is wrong. In that case, either try removing AFTER c1 or try using BEFORE c3 instead of AFTER c1.

edited Jul 09 '14 at 23:40

answered Jul 09 '14 at 23:16

Rahul

76,197
13
71
125

That also doesn't help. It gives me the same error. – principal-ideal-domain Jul 09 '14 at 23:17
1

Hmm ... then the Spec is wrong. BTW, try removing `after c1` and see. Or instead of saying `after c1` .. say `before c3` – Rahul Jul 09 '14 at 23:23
Ahh!! great. Then it's time for accepting the answer :) – Rahul Jul 09 '14 at 23:38

score 0 · Answer 3 · answered Jul 09 '14 at 23:38

0

Due to Rahul I've found a way to solve a problem although the solution doesn't tell me why my first approach didn't succeed.

Instead of using "after" using "before" doesn't invoke error messages:

alter table t1 add column c2 bool before c3;

answered Jul 09 '14 at 23:38

principal-ideal-domain

3,998
8
36
73

Adding a column to table with SQL

3 Answers3