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:

--  -----------------------
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


--  -----------------------  -------
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?


1 Answer 1


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

    id, version_date,
    row_number() over (partition by id order by version_date) as version
    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.

  • This is exactly what I need! You are right that this is an insert only table (and reads). What I need to do is deal with conflicting inserts. Each transaction will attempt to increment the version number and insert a row. A constraint violation on (id, version) will tell me that 2 transactions have collided.
    – Darren
    Commented Feb 12, 2015 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.