Originally posted by Raminder
So, the best way to increment ID (without gaps) would be to create a function that adds 1 to the last ID (max id selected from table) and use this function in the insert statement. The (first row has to be initialized with 1).
You have to be carefull with this aproach too! There are good chances that two concurent users will get the same max id from that table if they query it at approximately the same time, so they will end up with inserting the same id into the table!

A better way would be to create a separate one-column-one row table that will hold the current max value of the ID. Then you would use:

SELECT last_value + 1 FROM seq_table
FOR UPDATE OF last_value NOWAIT;

UPDATE seq_table SET last_value := last_value + 1;

The crutial thing here is to use SELCT FOR UPDATE NOWAIT you update the last_value with higher value. This prevents you to read current last_value from a table if someone else alreday have a lock on that row and at the same time it locks that row untill you commit your update of the last_value. However this kind of "manual sequence generator" imposes serialisation, noone can perform anything involvng any work with seq_table from the moment you lock it with SELECT FOR UPDATE until you commit or rollback your transaction. In an OLTP environment this kind of serialisation could be unacceptable.