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 |
I expected that nextval()
will get next id from sequence, but nextval increment it by 'default' value.
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.
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.