I
I
Ivan Palamarchuk2016-11-13 01:27:10
PostgreSQL
Ivan Palamarchuk, 2016-11-13 01:27:10

Whether it is possible at the conflict (INSERT) not to update SEQUENCE?

There is a table in which, to avoid duplicates, a count column is created, which is updated every time you try to add a duplicate, all this is implemented based on the unique index of these columns.
To add/update I use the syntax:

INSERT INTO table (..columns)
VALUES (..columns)
ON CONFLICT (..columns) DO
    UPDATE SET count = table.count + 1

And every time UPDATE is called, the SEQUENCE value changes to n + 1.
Is it possible to influence this process somehow?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
R
Rsa97, 2016-11-13
@Rsa97

This behavior is standard for most DBMS. Before starting the INSERT, the next value for SERIAL is requested, and the DBMS increments the counter by one. But, since several more requests could require the same counter during the execution of the request, unused values ​​are not accepted back and gaps are formed.
You can make your own counter, but at the cost of performance ( www.varlena.com/GeneralBits/130.php). As a rule, this makes no sense.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question