Auto Increment Index Field For Create and Update 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?

Thanks!
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.