PostgreSQL getting the latest value

Question

I have started working on a postgreSQL database. There is a table keeping the record of stocks and every time a record is updated, a copy of the original row needs to be kept, with a flag added to say that the record is no longer current. I am planning to implement this using a Trigger (which I am assuming is the best option).

In the above case, with a flag to say that a record is not the current one, what's the best way to ensure that reads from the table always used the latest records? Only checking the flag value with where condition?

Answer 1

If it were me, rather than flagging current, I would just time stamp each entry. Then, you would not need to update anything. You would only need to insert a new row. This way, you can query sets of data to plot against time, as a side benefit.