Design table such that each item has only one "current" stock value

See more:

database

PostgreSQL

I have a Database where one table records the stock of items. On purchase of items, the requisite amount is added to stock while on sale, requisite amount is deducted.

The columns of the table is as follows:

Item(F.key)----Qty----Date----IsCurrent

The item attribute is a foreign key to the items table. Quantity shows the stock at the given date.

IsCurrent is a boolean that indicates whether the given stock is the most current one.

So, at any given time, for any particular item, there can be only one record with IsCurrent set to true.

What I have tried:

I have used an index:

CREATE UNIQUE INDEX onlyonecurrent_index
    ON mycompany.stock USING btree
    (item COLLATE pg_catalog."default")
    TABLESPACE pg_default    WHERE iscurrent
;

This takes care of the constraint. However, it means inserting records to this table is a hassle. I have to find the current stock, clear the IsCurrent flag and then enter the new entry.

I have thought of two ways to correct the problem. I can maintain two tables, one StockHistory and another CurrentStock, where the archived data will be put in StockHistory.

Or, I can simply remove the IsCurrent flag and sort the items by date to get the most current entry.

However I am not fond of any of those approaches.

Is there any tried and tested way to handle a problem like this?