I'm trying to create an unique auto increment index field that is re-generated on both inserts and updates. I need it so that a client can send a server an integer and get back all outdated (created or updated) records. Something that would allow:

Insert Record A (Record A 'touched' set to '1')  
Insert Record B (Record B 'touched' set to '2')
Update Record A (Record A 'touched' set to '3')
Update Record B (Record B 'touched' set to '4')

I'm using both Postgresql and Sqlite (production and development) and would ideally like a solution that will work under both. What is the best way to add something like this?


  • a) I'm curious about that business logic. b) sounds like the need for a trigger. – jcolebrand May 30 '11 at 6:11
  • @jcolebrand The purpose is to do a offline pull onto mobile devices of a data set. The pull needs to grab all created and modified records since the last pull. – Kevin Sylvestre May 30 '11 at 6:31
  • 8
    It is not a very good idea to develop and test with a different DBMS than the one used in production. – a_horse_with_no_name May 30 '11 at 8:47

You need to combine a sequence with some mechanism to trigger the increment for an update. For postgres

create table my_table(id serial primary key, description text, touched serial);

Then either:

  • create a trigger to set touched = default on an update or
  • get all your updates going through an API that also actions the increment

auto-increment is implemented differently for SQLite so you will have to work around that - perhaps by inserting/deleting a new row each time you update a row, and using the rowid of that temporary row to update touched.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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