Обсуждение: Re: Behavior of identity columns
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 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
On Thu, 4 Aug 2022 at 10:52, sivapostgres@yahoo.com <sivapostgres@yahoo.com> wrote: > I see 'identity' column values increment even when some error(s) occurs while inserting data. Is that a known bug or knownbehavior? Known behaviour, explained thousand of times over the years. Identity is not a way to generate consecutive values, not ever guaranteed ascending values, it is for generating unique values. The implementation burn the values before commit/rollback for better concurreency, search archives or docs for details. In your example, id is allocated and burnt to generate the complete failing row, when it fails it is gone. Search lists, google, the docs, its been explained ad nauseam. Francisco Olarte.