H2 - Create unique index with two columns

Question

currently I am using a postgres query to create two unique indexes.

Each index consists of two columns, where the value of one column is checked for null/not null:

CREATE UNIQUE INDEX deleted_not_null_idx 
ON user (ADDRESS, DELETED)
WHERE DELETED IS NOT NULL;

CREATE UNIQUE INDEX deleted_null_idx 
ON user (ADDRESS)
WHERE DELETED IS NULL;

I am attempting to do the same on H2 but I am having issues understanding the syntax and structure of H2.

How would this expression be formed if written using H2 syntax?

Answer 1

A workaround to ensure "uniqueness of the columns on a subset of rows" can be worked out if you are willing to add an artificial extra column to the table, just for this purpose. Not sure it's the best idea, but can do the job.

For example:

create table t (
  address varchar(20),
  deleted int,
  extra_column varchar(20) as
    case when deleted is null then null else address end,
  constraint uq1 unique (extra_column)
);

insert into t (address, deleted) values ('123 Maple', 20);
insert into t (address, deleted) values ('456 Oak', 25);
insert into t (address, deleted) values ('456 Oak', null); -- succeeds
insert into t (address, deleted) values ('456 Oak', 28); -- fails

Result:

select * from t;

ADDRESS    DELETED  EXTRA_COLUMN
---------  -------  ------------
123 Maple       20  123 Maple   
456 Oak         25  456 Oak     
456 Oak     <null>  <null>