Postgresql unique index: only one date for one foreign key

Question

I have a table "foo":

ID   PRODUCT_ID  END_DATE
------------------------------
1    1           NULL
2    1           2022-02-02
3    1           2022-01-06 - This date could not be exists 
4    2           NULL
5    2           2022-01-23
6    3           NULL
7    3           NULL   

How can i make unique index for one product whith the same id only one date can exist?

Answer 1

Create a conditional unique index:

CREATE UNIQUE INDEX u_productid_end_date_not_null
ON foo(product_Id) 
WHERE end_date IS NOT NULL; -- this one will do the trick