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?

Solution 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.

Solution 2

If you want to insert a record into the table with serial column - just ommit it from the query - it will be automaticaly generated.

Or you can insert its defaul value with something like:

insert into your_table(id, val)
values (default, '123');

Third option is to malually take values from serial sequence directly:

insert into your_table(id, val)
values (nextval(pg_get_serial_sequence('your_table','id')), '123');

Solution 3

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

This approach is totally wrong and won't work in any database. It's only worked for you so far in MySQL by sheer luck.

If two connections simultaneously run this, they'll get the same ID. It can only work reliably if you lock the table against concurrent reads so that only one connection can be getting an ID at a time.

It's also terribly inefficient.

This is why sequences exist, so that you can reliably get IDs in the presence of concurrent inserters.

Just use:

INSERT INTO my_table(data1, data2) VALUES ('a','b') RETURNING id;

or:

INSERT INTO my_table(id, data1, data2) VALUES (DEFAULT, 'a','b') RETURNING id;

DEFAULT is a special place-holder that tell the database to get the default for that column from the table definition. The default is nextval('my_table_id_seq'), so the next sequence value will get inserted.

Since you're asking basic questions about sequences, I recommend you also consider that sequences are not gapless. It's normal for sequences to have "holes", where the table values go 1, 3, 4, 5, 9, 10, ... .

Comments

[Raymond Nijland]

Serial is PostgreSQL this a autoincrementing four-byte integer, you should NOT generate your own numbers if id is an serial. in MySQL this is a AUTO_INCREMENT column more or less..

[zod]

thanks... so its different from mysql..is it? is the sequence works same in oracle and sql server?

[zod]

thanks . i think there is no extra sequence in mysql. its just the auto increment id. so max(id)+1 will work in mysql but not in postgres!

[Ihor Romanchenko]

@zod It is the same in Oracle, but I have no expiriens with MS SQL Server.

[zod]

but it should have trigger serial sequesnce increment ? whats your suggestion? i know its not like that. just asking a suggestion

[mu is too short]

My suggestion is in my answer: leave serial and auto_increment values to the database. Don't mess with them at all. If you let the database assign the serial value then the sequence will be updated and everything will be fine; if you manually assign a value then you have to manually update the sequence.

[Ihor Romanchenko]

@zod Manual inserts to the postgres DB do not trigger any changes to sequences. Why should they?

[Morey]

great answer on default value insert for SERIAL type when columns are not specified. I've been looking for Postgres' equivalent of 'null' insert for AUTO_INCREMENT in MySQL. Thanks.