#364 Add compatibility support with postgres index

[Pavel Moukhataev]

We'd like to start using hsqldb as fast and lightweight engine that is used in our tests in postgres compatibility mode. But there are some compatibility issues that prevent using hsqldb as postgres replacement.

For example in postgresql it is possible to define unique index with condition:

CREATE TABLE IF NOT EXISTS project (
    id        bigserial PRIMARY KEY,
    key       short_text NOT NULL,
    person_id bigint
);

CREATE UNIQUE INDEX project_key ON project (key) WHERE person_id IS NULL;

As far as I understood in hsqldb it is possible to create either UNIQUE or CHECK CONSTRAINT but not possible to create UNIQUE and CHECK CONSTRAINT.

Can you please add support for conditional index.

Also are there any plans in enhancing postgres compatibility support?

[Fred Toussi]

Some non-standard features of PostgreSQL are not supported. Conditional indexes are used to reduce index size and improve INSERT performance. I don't think a UNIQUE conditional index is really useful, even in PostgreSQL.

You can create an ordinary non-unique index and use CREATE TRIGGER with BEFORE INSERT and BEFORE UPDATE to check for pre-existing rows.