Does Postgresql SERIAL work differently?

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.

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');

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