Aram
Aram

Reputation: 45

How to create a unique index in PostgreSQL with subquery?

I use PostgreSQL and try to add the index below:

CREATE UNIQUE INDEX product_type_serial_pin_unique
ON servicestore.product_unit(serial)
WHERE serial is not null AND product_id = (SELECT id FROM servicestore.product WHERE product_type = 'SERIAL_PIN');

I got the following error:

ERROR:  cannot use subquery in index predicate

how can I solve this problem?

Upvotes: 0

Views: 645

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247300

The only way you can make that work with a constraint is if you add an attribute to product_unit that copies product_type from product. To make sure they are in sync, add the attribute to a foreign key from product_unit to product. Alternatively, you can use triggers to synchronize the redundant data.

Upvotes: 1

Related Questions