How can I add a numeric version_id column to an existing table?

Question

I have a table in a postgres db with rows versioned with a timestamp that I'd like to add a numeric version_id column to.

eg. existing:

ID  VERSION_DATE
--  -----------------------
A   2015-02-05 21:25:56.123
A   2015-02-06 21:15:56.456
A   2015-02-07 11:25:52.789
B   2015-01-01 13:44:54.223

desired:

ID  VERSION_DATE             VERSION
--  -----------------------  -------
A   2015-02-05 21:25:56.123  1
A   2015-02-06 21:15:56.456  2
A   2015-02-07 11:25:52.789  3
B   2015-01-01 13:44:54.223  1

Is there a query that can calculate and insert the version values?

Answer 1

This is exactly what ROW_NUMBER() window function can do:

select 
    id, version_date,
    row_number() over (partition by id order by version_date) as version
from
    your_table ;

I don't see a reason to have these version numbers stored in a column of the table. It would be fairly easy to insert the values the first time but quite complicated to keep them updated after every insert, delete and update operation (probably done via triggers or stored procedures).

It might not be that complicated if no rows are ever deleted and the version_date is never updated. That would mean that only during inserts the new (inserted) values would have to be correctly calculated.

Another option, would be to use a materialized view.