CODEWITHSUNDEEP
postgresqlupsertsequence-sql
jr-scalawag
jr-scalawag

Reputation: 11

Postgresql upsert statement increments the sequence last_value when updating records

I noticed a postgresql table with a primary key id field of type bigserial had an id with a value in the millions but only had a COUNT showing a few hundred records. The table was being called regularly with an upsert statment similar to the following: INSERT INTO test_table (ticker,column1) VALUES ('a','1') ON CONFLICT (ticker) DO UPDATE SET column1=EXCLUDED.column1;

And I found that if the ticker already existed, the sequence was being updated and when a record didn't already exist, the upsert would give the id the value of the sequence last_value + 1.

This did not seem like normal behavior to me that the sequence last_value would be incremented when an upsert statement updated a record instead of inserting it.

The only workaround I can think of is to reset the sequence after every upsert statement with a statement something like this: SELECT setval('test_table_id_seq', (SELECT MAX(id) FROM test_table));

Any input would be welcome.

To look into this behavior I created a simple db with 1 table as follows:

CREATE TABLE public.test_table (
    id bigserial NOT NULL,
    ticker varchar(10) NOT NULL,
    column1 varchar(10) NULL,
    CONSTRAINT test_table_pk PRIMARY KEY (id),
    CONSTRAINT test_table_unique UNIQUE (ticker) 
);

This automatically created a sequence with the name test_table_id_seq. As expected the last_value field in test_table_id_seq was 1.

Then I used an upsert statement to insert a record because it didn't already exist:

INSERT INTO test_table (ticker,column1) VALUES ('a','1') ON CONFLICT (ticker) DO UPDATE SET column1=EXCLUDED.column1;

As expected the last_value field in test_table_id_seq was still 1 and the primary key id field for the new record in the test_table was 1.

Then I ran some statements updating the existing record:

INSERT INTO test_table (ticker,column1) VALUES ('a','2') ON CONFLICT (ticker) DO UPDATE SET column1=EXCLUDED.column1;
INSERT INTO test_table (ticker,column1) VALUES ('a','3') ON CONFLICT (ticker) DO UPDATE SET column1=EXCLUDED.column1;
INSERT INTO test_table (ticker,column1) VALUES ('a','4') ON CONFLICT (ticker) DO UPDATE SET column1=EXCLUDED.column1;

And then saw the unexpected behavior where the last_value in test_table_id_seq had been incremented to 4 but should have been 1 because no new records were inserted and the highest value in the primary key id field of the test_table was 1, not 4.

And of course when I added a new record as follows it had the id value of 5 because the sequence last_value was 4:

INSERT INTO test_table (ticker,column1) VALUES ('b','1') ON CONFLICT (ticker) DO UPDATE SET column1=EXCLUDED.column1;

I was expecting that updating an existing record would not increment the sequence so that when a new record is inserted it would have a primary key id equal to the highest previous id + 1. So this behavior was a surprise.

Or course when I redid the entire experiment but put the following statement after every single upsert statement, the id field values were all incremented by 1 as I originally had expected:

SELECT setval('test_table_id_seq', (SELECT MAX(id) FROM test_table));

Is this behavior normal? Am I missing something?

Upvotes: 1

Views: 270

Answers (0)

Related Questions