postgresql - How to annotate unique constraint with WHERE clause in JPA

Question

I need to use these unique constraints in PostgreSQL

CREATE UNIQUE INDEX favorites_3col_uni_idx
ON favorites (user_id, menu_id, recipe_id)
WHERE menu_id IS NOT NULL;

CREATE UNIQUE INDEX favorites_2col_uni_idx
ON favorites (user_id, recipe_id)
WHERE menu_id IS NULL;

The first one I annotate in JPA:

@Table(uniqueConstraints= {
@UniqueConstraint(name="favorites_3col_uni_idx", columnNames = {"user_id", "menu_id", "recipe_id"})
})

But, ?it is possible to annotate in JPA the second unique index?

Thx.

Answer 1

You appear to want to create partial indexes (CREATE INDEX ... ON ... WHERE) using JPA constraint definitions.

These are fairly PostgreSQL specific, and aren't specified by JPA. You will need to use native syntax to create them. I don't believe JPA offers any features for index definition.

You cannot use a unique constraint for this purpose because unique partial indexes are not unique constraints. Partial unique indexes cannot be created with CONSTRAINT constraint_name UNIQUE(columns) in PostgreSQL. It's only an implementation detail that PostgreSQL creates a unique index for a unique constraint at all.

See:

• Specifying an Index (Non-Unique Key) Using JPA
• JPA: defining an index column

Some JPA providers offer extension annotations specific to that JPA provider that add features for running native DDL scripts, defining indexes with annoations, etc. Since you haven't mentioned which JPA provider you are using I can't tell you more. Here's the documentation for EclipseLink index DDL; this will not work if you are using Hibernate, OpenJPA, or something other than EclipseLink.

A JPA standard workaround is to check for the presence of those indexes during startup by querying pg_catalog.pg_index. If you don't find them, use an EntityManager native query to send the appropriate native SQL CREATE UNIQUE INDEX commands. A @Startup @Singleton bean is useful for this sort of task if you're using EJB3.1. See the PostgreSQL documentation for the structure of pg_catalog.pg_index. To just check if an index of a given name exists, run:

SELECT EXISTS(
    SELECT 1 
    FROM pg_index 
    WHERE indexrelid = 'public.indexname'::regclass
);

Note that the above query does nothing to verify it's the index you expect, but you can do that with some additional checks. Just examine the contents of pg_index after creating the index so you know what to test for. I don't recommend trying to check for any particular value of indpred; just make sure it isn't null.