additional question about postgresql 10 's new identity column

From: john snow <ofbizfanster(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: additional question about postgresql 10 's new identity column
Date: 2017-12-16 02:33:42
Message-ID: CAE67tvXEHNaxJumcJNnKAmH_urToSOUEXMm+sSXkpF2okhvCbQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

postgresql version we're using in windows 10:
PostgreSQL 10.0, compiled by Visual C++ build 1800, 64-bit

we have the following table named subclass:
\d stk_m.subclass
Table "stk_m.subclass"
Column | Type | Collation | Nullable | Default

---------------+---------------+-----------+----------+------------------------------
subclass_id | integer | | not null | generated always as
identity
subclass_code | character(3) | | |
subclass_desc | character(30) | | |
class_code | character(1) | | |
class_id | integer | | |
Indexes:
"subclass_pkey" PRIMARY KEY, btree (subclass_id)
"uq_subclass_code_class_id" UNIQUE CONSTRAINT, btree (subclass_code,
class_id)
Foreign-key constraints:
"subclass_class_class_id_fk" FOREIGN KEY (class_id) REFERENCES
stk_m.class(class_id)

the combined values for subclass_code and foreign key class_id must be
unique tablewise.

at primary key subclass_id = 260,
when we do two successive inserts that violate the unique constraint, then
do a valid insert,
we notice that the successful insert had subclass_id value of 263, so even
tho the prior two inserts failed, they consumed the 261 and 262 values for
the primary key.

is this the expected behavior? anything we can do or configure so that
failed inserts wont advance the nextval for the primary key?

thanks for any info / help!

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message David G. Johnston 2017-12-16 03:27:11 Re: additional question about postgresql 10 's new identity column
Previous Message Stephen Frost 2017-12-14 21:09:51 Re: r there downsides to explicitly naming a pk column xxxx_pk