Re: Behavior of identity columns - Mailing list pgsql-general

From sivapostgres@yahoo.com
Subject Re: Behavior of identity columns
Date
Msg-id 253496326.523793.1659603128227@mail.yahoo.com
Whole thread Raw
Responses Re: Behavior of identity columns
List pgsql-general
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

pgsql-general by date:

Previous
From: Mateusz Henicz
Date:
Subject: Re: BUG #17567: Unable to Set Max_Connection in Postgresql which has replicas
Next
From: Danny Shemesh
Date:
Subject: Index only scans for expressional indices when querying for the expression