How to create a unique index in PostgreSQL with subquery?

Question

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?

So what would you expect to happen if that sub-query returns a different result tomorrow? And what if that sub-query returns more than one product (which seems quite likely) — user330315
– user330315, Commented Feb 10, 2021 at 11:02

Laurenz Albe · Accepted Answer · 2021-02-10 11:54:02Z

1

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.

answered Feb 10, 2021 at 11:54

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create a unique index in PostgreSQL with subquery?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related