PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to get id from sequence without nextval() in postgres?

I’m trying to figure out how to insert new data into a database while explicitly setting the ID. However, when I use nextval('user_seq'), it doesn’t retrieve the next value from the sequence as expected; instead, it increments it based on the value I inserted earlier:
demo at db<>fiddle

CREATE TABLE users
(
    user_id        BIGINT NOT NULL,
    user_firstname VARCHAR(50),
    user_lastname  VARCHAR(50),
    user_nickname  VARCHAR(100),
    user_email     VARCHAR(255),
    user_password  VARCHAR(255),
    user_role      VARCHAR(20),
    CONSTRAINT pk_users PRIMARY KEY (user_id)
);
CREATE SEQUENCE IF NOT EXISTS user_seq START WITH 1 INCREMENT BY 50;

INSERT INTO users VALUES 
 (nextval('user_seq'), 'First Name', 'First Lastname', 'First Nickname', '[email protected]', 'First pass', 'USER_PRESENTER')
,(nextval('user_seq'), 'Second Name', 'Second Lastname', 'Second Nickname', '[email protected]', 'Second pass', 'USER_ELECTOR')
RETURNING *;
user_id user_firstname user_lastname user_nickname user_email user_password user_role
1 First Name First Lastname First Nickname [email protected] First pass USER_PRESENTER
51 Second Name Second Lastname Second Nickname [email protected] Second pass USER_ELECTOR

  1. I expected that nextval() will get next id from sequence, but nextval increment it by ‘default’ value.

  2. I thought about currval() but this method should be in context of query.

I also tried pg_get_serial_sequence() but I had error that I can’t use it in this query.

Maybe I did something wrong, I never used it before.

Tags:

2

Answers


  1. 0

    That is working just as it should. If you define a sequence with START WITH 1 INCREMENT BY 50, then the calls to nextval() for that sequence will return the values 1, 51, 101, 151, etc. If that is not what you want, you have to define the sequence differently.

    A sequence is a counter: the first call to nextval() returns the starting value, and on every subsequent call, the counter is incremented by the INCREMENT (default 1).

    Perhaps you should define the sequence with CACHE 50 instead of INCREMENT BY 50. Then each call to nextval() will add one to the previous result, as you seem to desire. The effect of CACHE 50 is that the first call to nextval() actually reserves 50 values from the sequence, and the following 49 calls to nextval() will use these cached values. The benefit is that the database backend does not have to access the sequence on every nextval() call, which improves the performance if there are many calls to nextval().

    Login or Signup to reply.
  2. 0

    The nextval() function behaves as it should, given how you defined the sequence – What Does the nextval() Function Do in PostgreSQL

    If you want to increment by 1, define the sequence accordingly:

    CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;

    Perhaps you meant to use the CACHE parameter instead of INCREMENT BY.

    CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1 CACHE 50;

    More details about creating sequence and the parameters.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search