Why is the serial primary key incrementing despite a using "On Conflict Do Nothing?

I have the below table in PostgreSQL 13:
table name: newtable

field       type 
-----       ----    
Seq         bigserial   
code        varchar

Seq is the primary key (auto-increment)

Code is a unique key index

Insert Into newtable (Code) Values ('001') On Conflict(Code) Do Nothing   --> Seq value is 1
Insert Into newtable (Code) Values ('001') On Conflict(Code) Do Nothing
Insert Into newtable (Code) Values ('001') On Conflict(Code) Do Nothing

Insert Into newtable (Code) Values ('002') On Conflict(Code) Do Nothing   --> Seq value is 4

Why is Seq 4? Is there any way to increase value on successful inserts only?

Use MERGE instead?

MERGE (added with Postgres 15) looks similar on the surface, but is quite different under the hood. The manual:

When MERGE is run concurrently with other commands that modify the
target table, the usual transaction isolation rules apply; see
Section 13.2 for an explanation on the behavior at each isolation
level. You may also wish to consider using INSERT ... ON CONFLICT as
an alternative statement which offers the ability to run an UPDATE
if a concurrent INSERT occurs. There are a variety of differences
and restrictions between the two statement types and they are not
interchangeable.

In particular, MERGE cannot reliably suppress unique violations under concurrent write load, like INSERT ... ON CONFLICT DO NOTHING can (the command in question). The manual:

If MERGE attempts an INSERT and a unique index is present and a
duplicate row is concurrently inserted, then a uniqueness violation
error is raised; MERGE does not attempt to avoid such errors by
restarting evaluation of MATCHED conditions.

A minor (and typically unimportant) side effect of the different approach is that MERGE does not normally burn serial numbers. (It still can, like when an exception is raised ...)

If you don't have concurrent write load, or if the discussed issues don't matter to you, then MERGE can be an alternative. (Then you probably didn't need INSERT ... ON CONFLICT DO NOTHING in the first place.) Rudi added an answer with a code example.
Your questions about INSERT ... ON CONFLICT DO NOTHING

Why does the Seq value keep increasing?

The reason is that DEFAULT values (and triggers and anything else that might change row values) are applied before checking for duplicates (trying to enter index tuples). And serial numbers are designed to defend against race conditions under concurrent load. The underlying SEQUENCE does not "take back" numbers once it has been incremented. There are other scenarios that would burn serial numbers. So gaps in serial numbers are to be expected. As long as you don't burn numbers at a gigantic rate, this should not be a problem.

Is there any way to only increase the Seq value if it is successfully inserted?

Not without (more or less) seriously compromising performance, like by using SERIALIZABLE transaction isolation or manual locking strategies. That's the reason why the ON CONFLICT clause ("UPSERT") exists in the first place.

If you don't actually have concurrent writes to the same table (are you sure?), this alternative query would avoid burning serial numbers:

INSERT INTO newtable (code)
SELECT '001'
WHERE  NOT EXISTS (SELECT FROM newtable WHERE code = '001';

It's slightly more expensive in the non-conflicting case as it first checks for existence in the index and then actually enters the new row in table and index(es). But slightly faster for conflicting cases. There is a tiny window between checking and writing where race conditions can cause problems under concurrent write load. That's when we use an UPSERT.

Related:

• Sequential increment skipping numbers

• Insert if names do not exist and return ids if exist

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

• How to use RETURNING with ON CONFLICT in PostgreSQL?

StackExchange Database Administrators Q#295341, answer score: 8