serial in postgres is being increased even though I added on conflict do nothing

The reason this feels weird to you is that you are thinking of the increment on the counter as part of the insert operation, and therefore the "DO NOTHING" ought to mean "don't increment anything". You're picturing this:

1. Check values to insert against constraint
2. If duplicate detected, abort
3. Increment sequence
4. Insert data

But in fact, the increment has to happen before the insert is attempted. A SERIAL column in Postgres is implemented as a DEFAULT which executes the nextval() function on a bound SEQUENCE. Before the DBMS can do anything with the data, it's got to have a complete set of columns, so the order of operations is like this:

1. Resolve default values, including incrementing the sequence
2. Check values to insert against constraint
3. If duplicate detected, abort
4. Insert data

This can be seen intuitively if the duplicate key is in the autoincrement field itself:

CREATE TABLE foo ( id SERIAL NOT NULL PRIMARY KEY, bar text );-- Insert row 1INSERT INTO foo ( bar ) VALUES ( 'test' );-- Reset the sequenceSELECT setval(pg_get_serial_sequence('foo', 'id'), 0, true);-- Attempt to insert row 1 againINSERT INTO foo ( bar ) VALUES ( 'test 2' )     ON CONFLICT (id) DO NOTHING;

Clearly, this can't know if there's a conflict without incrementing the sequence, so the "do nothing" has to come after that increment.

As already said by @a_horse_with_no_name and @Serge Ballesta serials are always incremented even if INSERT fails.

You can try to "rollback" serial value to maximum id used by changing the corresponding sequence:

SELECT setval('sometable_id_seq', MAX(id), true) FROM sometable;

As said by @a_horse_with_no_name, that is by design. Serial type fields are implemented under the hood through sequences, and for evident reasons, once you have gotten a new value from a sequence, you cannot rollback the last value. Imagine the following scenario:

• sequence is at n
• A requires a new value : got n+1
• in a concurrent transaction B requires a new value: got n+2
• for any reason A rollbacks its transaction - would you feel safe to reset sequence?

That is the reason why sequences (and serial field) just document that in case of rollbacked transactions holes can occur in the returned values. Only unicity is guaranteed.