sqlpostgresqlsequence

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', 'first@email.gg', 'First pass', 'USER_PRESENTER')
,(nextval('user_seq'), 'Second Name', 'Second Lastname', 'Second Nickname', 'second@email.gg', '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 first@email.gg First pass USER_PRESENTER
51 Second Name Second Lastname Second Nickname second@email.gg 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.


Solution

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