Hello,
I see 'identity' column values increment even when some error(s) occurs while
inserting data. Is that a known bug or known behavior?
Create script of table:**************************** 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
columncolumn 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 SQLinsert into users (username, email, first_name, last_name) values
('ONE', '[email protected]', 'one', '1'); // id = 1insert into users (username,
email, first_name, last_name) values ('ONE', '[email protected]', 'one', '1'); //
Insert failsinsert into users (username, email, first_name, last_name) values
('TWO', '[email protected]', 'one', '1'); // id = 3insert into users0(username,
email, first_name, last_name) values ('TWO', '[email protected]', 'one', '1'); //
insert fails as there is no users0 tableinsert into users (username, email,
first_name, last_name) values ('THREE', '[email protected]', '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 AlwaysBKR Sivaprakash
