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!
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 |