Does Postgresql SERIAL work differently?

Question

I have a postgres table with a SERIAL id.

id (serial) name age 

Insert usually happens from a web application.

I inserted manually two new records setting the id as max (id)+1****

After these 2 insert when the web app inserts 2 record it gives duplicate key error.

Just for 2 records. After that everything works fine.

The question is - Why didn't my manual insert increment the serial?

Are auto increment and serial are different?

What am I missing here? Do MySQL or any other SQL have the same issue?

Answer 1

When you create a serial or bigserial column, PostgreSQL actually does three things:

1. Creates an int or bigint column.
2. Creates a sequence (owned by the column) to generate values for the column.
3. Sets the column's default value to the sequence's nextval().

When you INSERT a value without specifying the serial column (or if you explicitly specify DEFAULT as its value), nextval will be called on the sequence to:

1. Return the next available value for the column.
2. Increment the sequence's value.

If you manually supply a non-default value for the serial column then the sequence won't be updated and nextval can return values that your serial column already uses. So if you do this sort of thing, you'll have to manually fix the sequence by calling nextval or setval.

Also keep in mind that records can be deleted so gaps in serial columns are to be expected so using max(id) + 1 isn't a good idea even if there weren't concurrency problems.

If you're using serial or bigserial, your best bet is to let PostgreSQL take care of assigning the values for you and pretend that they're opaque numbers that just happen to come out in a certain order: don't assign them yourself and don't assume anything about them other than uniqueness. This rule of thumb applies to all database IMO.

---

I'm not certain how MySQL's auto_increment works with all the different database types but perhaps the fine manual will help.