I see 'identity' column values increment even when some error(s) occurs while inserting data. Is that a known bug or known behavior?
CREATE TABLE public.users
(
id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
username character varying(255) COLLATE pg_catalog."default",
email character varying(255) COLLATE pg_catalog."default",
first_name character varying(255) COLLATE pg_catalog."default",
last_name character varying(255) COLLATE pg_catalog."default",
CONSTRAINT users_pkey PRIMARY KEY (id),
CONSTRAINT "test_UX" UNIQUE (username)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.users
OWNER to postgres;
***********************************
column id is int and identity column
column username is unique
When I insert data that violates the unique constraint, I see that the id value gets incremented. And I see missing values in between when I add the correct data next time.
my Insert SQL
insert into users (username, email, first_name, last_name) values ('ONE', 'one@gmail.com', 'one', '1'); // id = 1
insert into users (username, email, first_name, last_name) values ('ONE', 'one@gmail.com', 'one', '1'); // Insert fails
insert into users (username, email, first_name, last_name) values ('TWO', 'one@gmail.com', 'one', '1'); // id = 3
insert into users0(username, email, first_name, last_name) values ('TWO', 'one@gmail.com', 'one', '1'); // insert fails as there is no users0 table
insert into users (username, email, first_name, last_name) values ('THREE', 'one@gmail.com', 'one', '1'); // id = 5
ID gets incremented even when some wrong table name is mentioned in the query. Check 4th query.
Is it a known behavior or a known bug?
Happiness Always BKR Sivaprakash