When you write a PostgreSQL-appliction, you often need to fetch the id of inserted rows. In the past years, I've used the following three appoaches.
The person table is used as an example and has an auto-incremental primary key:
A sequence will automatically be created for the person_id column.
Now we want to insert a person and get his person_id:
The person will be inserted and you get his person_id. But only as long as you are the only user inserting in the person table. When there are concurrent inserts, the chance is high to get a wrong id with this method.
The next method is a little better:
This approach uses a transaction and the lastval() function. The generated id will be stored in a transactionvariable and is concurrent proof.
I recently added a lot of triggers to my database and suddenly I was getting invalid id's with the lastval() function. This happens only if a sequence is incremented in the trigger.
The last transactionless approach is concurrent- and triggerproof. This is the best Method:
