Re: add column to table if not exists

Although we can add IF NOT EXISTS to most database object creation statements, this is not possible for ALTER statements.

It is also not possible to use DDL statements inside a PROCEDURE.

We may add one of these capabilities to the next version, 2.5.0, in the coming days.

Fred Toussi

On Sat, May 4, 2019, at 06:50, [email protected] wrote:
> Trying to add column column1 to table1 if it doesn't exist yet:
> 
> create table if not exists table1( column2 varchar(20));
> 
> CREATE PROCEDURE column_present()
>  MODIFIES SQL DATA
>  BEGIN ATOMIC
>  DECLARE column_count integer;
>  set column_count = select COUNT(*) from information_schema.system_columns Where table_name = 'table1' and column_name = 'column1';
>  if column_count = 0 then alter table table1 ADD column1 integer; end if;
> END;
> 
> call column_present();
> 
> results in:
> 
> [2019-05-03 22:28:13] [42581][-5581] unexpected token: ALTER : line: 6
> [2019-05-03 22:28:13] java.lang.RuntimeException: org.hsqldb.HsqlException: unexpected token: ALTER : line: 6
> [2019-05-03 22:28:13] at org.hsqldb.error.Error.parseError(Unknown Source)
> 
> what is the proper way to create the column in existing table (if it doesn't exist yet) in the HSQLDB?
> 
> Please note: ignoring the error on creation if it already exists, is not an option for me.
> 
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user