0

I am currently upserting to a target table, using ON CONFLICT(), but I noticed that when I upsert the my primary key id column which is using a GENERATED BY DEFAULT AS IDENTITY jumps for the number of times a conflict happens. Is there a way to make it not skip numbers or is this by design?

CREATE TABLE Mytable (id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 
col1 VARCHAR(100),col2 VARCHAR(100), CONSTRAINT unique_col1_col2 UNIQUE (col1,col2));

INSERT INTO mytable (col1,col2) VALUES ('Song1','Artist1'),
('Song2','Artist2'),('Song3','Artist3'),('Song4','Artist4');

INSERT INTO mytable (col1,col2) VALUES ('Song1','Artist1'),
('Song2','Artist2'),('Song3','Artist3'),('Song4','Artist4'),
('Song5','Artist5') ON CONFLICT (col1, col2) DO NOTHING;

enter image description here

dko512
  • 423
  • 3
  • 11
  • 1
    Short version, sequences(the thing that backs `IDENTITY`) are not guaranteed to be gapless and with certain settings not sequential either. For longer version see posted link. – Adrian Klaver Aug 28 '21 at 22:49

0 Answers0