Postgresql – Auto Increment Index Field For Create and Update

postgresqlsqlite

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!

Best Answer

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.

Related Solutions

Postgresql – psql: id SERIAL PRIMARY KEY not starting with 1

The table should be

create table cali (
    id serial primary key,
    alk_from bigint,
    alk_to bigint,
    and_from int,
    and_to int
);

And do

\COPY cali (alk_from, alk_to, and_from, and_to) 
FROM '/home/.../data/output/id_cali.csv' 
(FORMAT CSV);

So that it knows to not insert into the id serial column. The serial type is not a true type. From the manual:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases)

Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator

SQLite – How to Create an ‘int’ Column Forced Between 0 and 3

thank you for your answers. I am using SQLite3 local on my laptop for testing purpose, I am not sure what the "real" database will be. So for that reason and because it does not seem easy to restrict values in a certain range, I have solved my problem in the software itself.

Using the "AND" condition to check if values are in the interval I want: "UPDATE xxx SET xxx WHERE xx AND xx"

Thank you all.

Best Answer

Related Solutions

Postgresql – psql: id SERIAL PRIMARY KEY not starting with 1

SQLite – How to Create an ‘int’ Column Forced Between 0 and 3

Related Question