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 stategies. 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:
