Postgres Sequence Value Not Incrementing After Inserting Records

Question

I have the following table:

DROP TABLE IF EXISTS TBL_CACL;
CREATE TABLE TBL_CACL (
   ID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   NAME VARCHAR(250)  UNIQUE NOT NULL
);

I am able to query postgres likes this:

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

to determine that the default name for the sequence on the table is tbl_cacl_id_seq

I can then read its nextVal like this:

SELECT nextval('tbl_cacl_id_seq');

Directly after creating the table the value is '1'

However, if I insert several rows of data like this:

INSERT INTO TBL_CACL
VALUES
(1, 'CACL_123'),
(2, 'CACL_234'),
(3, 'CACL_345');

and I read nextVal for the table, it returns '2'

I would have thought that tbl_cacl_id_seq would be '4'. Clearly I misunderstanding how the insert is related to the nextVal. Why is the sequence out of sync with the inserts and how do I get them in sequence? Thanks for any advice.

Nizar M · Accepted Answer · 2021-02-19 02:53:59Z

2

The tbl_cacl_id_seq gets incremented only when the function nextval('tbl_cacl_id_seq') is called. This function call will not happen since you have provided values for ID column during insert (hence no need to get the default value).

answered Feb 19, 2021 at 2:53

Nizar M

3892 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurenz Albe Over a year ago

Exactly. If you use GENERATED ALWAYS AS IDENTITY, it will become clear what is going on.

Timothy Clotworthy Over a year ago

Brilliant!. I get it now. Thanks!

Collectives™ on Stack Overflow

Postgres Sequence Value Not Incrementing After Inserting Records

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related