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?
row_number() over (partition by id order by version_date)