CODEWITHSUNDEEP
postgresql
Timothy Clotworthy
Timothy Clotworthy

Reputation: 2452

Postgres Sequence Value Not Incrementing After Inserting Records

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.

Upvotes: 2

Views: 1693

Answers (1)

Nizar M
Nizar M

Reputation: 379

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).

Upvotes: 2

Related Questions