Postgres primary key allowing collision

Question

The table should have behavior similar to the primary key(Item_id, Date, Status). However, multiple cancel status is allowed.

Item_id  |Date       |Status   
--------------------------------
1        |2017-01-01 |Cancelled   
1        |2017-01-01 |Cancelled   
1        |2017-01-01 |Completed

In this case, I will be able to insert:

('1', '2017-01-01', 'Cancelled')

And in this case, the query should return error instead:

('1', '2017-01-01', 'Completed')

Is there a way to use constraint or other implementation to achieve this goal?

Answer 1

What you are asking is self conflicting. A primary key is by definition unique, thus it's impossible to have several rows with the same tuple (Item_id, Date, Status) if the primary key is (Item_id, Date, Status). For example, there would be no way to distinguish 2 rows if they are both identified by the same tuple (1, 2018-01-01, 'canceled')

It looks like you want to enforce a single completed row per tuple (item_id, date), which is doable: see this question. You will need to add a primary key column to your table.