Обсуждение: Automatic PK values not added to new rows
The People table has 965 rows; the table structure is:
Table "public.people"
Column | Type | Collation | Nullable | Default
---------------+-----------------------+-----------+----------+-------------------------------------
person_nbr | integer | | not null | nextval('people_person_nbr_seq'::reg
class)
lname | character varying(32) | | not null | '??'::character varying
fname | character varying(15) | | not null | '??'::character varying
job_title | character varying(48) | | |
company_nbr | integer | | |
loc_nbr | integer | | not null | 1
loc_phone_ext | character varying(32) | | |
direct_phone | character varying(15) | | |
direct_fax | character varying(15) | | |
cell_phone | character varying(15) | | |
email | character varying(64) | | |
active | boolean | | not null | true
comment | text | | |
Indexes:
"people_pkey" PRIMARY KEY, btree (person_nbr)
Foreign-key constraints:
"people_org_nbr_fkey" FOREIGN KEY (company_nbr) REFERENCES companies(company_nbr) ON UPDATE CASC
ADE ON DELETE RESTRICT
I'm trying to insert 15 new rows to that table by inserting all columns
except the first one.. Psql tells me:
psql:insert_into_people.sql:16: ERROR: duplicate key value violates unique constraint "people_pkey"
DETAIL: Key (person_nbr)=(683) already exists.
person_nbr 683 is not in any row to be inserted.
I was under the impression that the person_nbr for the new rows would start
with 966 but that's apparently not happening.
What have I missed?
Rich
On 5/25/22 10:43 AM, Rich Shepard wrote: > The People table has 965 rows; the table structure is: > > person_nbr 683 is not in any row to be inserted. > > I was under the impression that the person_nbr for the new rows would start > with 966 but that's apparently not happening. > > What have I missed? Do: select * from people_person_nbr_seq; and report back the results. > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wednesday, May 25, 2022, Rich Shepard <rshepard@appl-ecosys.com> wrote:
I'm trying to insert 15 new rows to that table by inserting all columns
except the first one..
That…
person_nbr 683 is not in any row to be inserted.
Implies that.
I was under the impression that the person_nbr for the new rows would start
with 966 but that's apparently not happening.
The value the sequence provides next is wholly independent of everything except the state of the sequence. It doesn’t care how many rows any table, even its owner, has. The very existence of the delete command should make this self-evident.
David J.
On Wed, 25 May 2022, Adrian Klaver wrote:
> Do:
> select * from people_person_nbr_seq;
> and report back the results.
Adrian,
Huh!
bustrac=# select * from people_person_nbr_seq;
last_value | log_cnt | is_called
------------+---------+-----------
683 | 32 | t
(1 row)
It's out of sync with
select max(person_nbr) from people;
Is there a way for me to synchronize the two?
Thanks,
Rich
On 5/25/22 11:15 AM, Rich Shepard wrote: > On Wed, 25 May 2022, Adrian Klaver wrote: > >> Do: >> select * from people_person_nbr_seq; >> and report back the results. > > Adrian, > > Huh! > bustrac=# select * from people_person_nbr_seq; > last_value | log_cnt | is_called ------------+---------+----------- > 683 | 32 | t > (1 row) > > It's out of sync with > select max(person_nbr) from people; What is max(person_nbr)? > > Is there a way for me to synchronize the two? > > Thanks, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, 25 May 2022, David G. Johnston wrote: > The value the sequence provides next is wholly independent of everything > except the state of the sequence. It doesn’t care how many rows any table, > even its owner, has. The very existence of the delete command should make > this self-evident. David J., I didn't know that. Thanks, Rich
On Wed, 25 May 2022, Adrian Klaver wrote: > What is max(person_nbr)? bustrac=# select max(person_nbr) from people; max ----- 965 (1 row) Rich
On 5/25/22 11:18 AM, Rich Shepard wrote: > On Wed, 25 May 2022, Adrian Klaver wrote: > >> What is max(person_nbr)? > > bustrac=# select max(person_nbr) from people; > max ----- > 965 > (1 row) From: https://www.postgresql.org/docs/current/functions-sequence.html SELECT setval('people_person_nbr_seq', 965); > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, 25 May 2022, Adrian Klaver wrote: > From: > https://www.postgresql.org/docs/current/functions-sequence.html > SELECT setval('people_person_nbr_seq', 965); Adrian, Thanks. I didn't know where to look in the docs. Regards, Rich
Rich Shepard schrieb am 25.05.2022 um 20:15: > On Wed, 25 May 2022, Adrian Klaver wrote: > >> Do: >> select * from people_person_nbr_seq; >> and report back the results. > > Adrian, > > Huh! > bustrac=# select * from people_person_nbr_seq; > last_value | log_cnt | is_called ------------+---------+----------- > 683 | 32 | t > (1 row) > > It's out of sync with > select max(person_nbr) from people; > > Is there a way for me to synchronize the two? If you want to prevent such a situation in the future, you might want to consider defining those columns as "generated always as identity" instead of "serial". Then you'd get an error if you try to bypass the database generated values.
On Wed, 25 May 2022, Thomas Kellerer wrote: > If you want to prevent such a situation in the future, you might want to > consider defining those columns as "generated always as identity" instead > of "serial". > > Then you'd get an error if you try to bypass the database generated values. Thanks, Thomas. Rich
On 5/25/22 13:17, Rich Shepard wrote: > On Wed, 25 May 2022, David G. Johnston wrote: > >> The value the sequence provides next is wholly independent of everything >> except the state of the sequence. It doesn’t care how many rows any table, >> even its owner, has. The very existence of the delete command should make >> this self-evident. > > David J., > > I didn't know that. people_person_nbr_seq would have to somehow peek into public.people and automatically update itself. I'd be hopping mad if Postgresql did that after I explicitly set the value of people_person_nbr_seq to the value of my choosing, as if Postgresql knows better than I do what I want the next value of people_person_nbr_seq to be. -- Angular momentum makes the world go 'round.
On 5/25/22 20:10, Ron wrote: > On 5/25/22 13:17, Rich Shepard wrote: >> On Wed, 25 May 2022, David G. Johnston wrote: >> >>> The value the sequence provides next is wholly independent of everything >>> except the state of the sequence. It doesn’t care how many rows any >>> table, >>> even its owner, has. The very existence of the delete command should >>> make >>> this self-evident. >> >> David J., >> >> I didn't know that. > > people_person_nbr_seq would have to somehow peek into public.people and > automatically update itself. I'd be hopping mad if Postgresql did that > after I explicitly set the value of people_person_nbr_seq to the value > of my choosing, as if Postgresql knows better than I do what I want the > next value of people_person_nbr_seq to be. Though to be honest if you are setting up an automatic id system, be it serial or identity, and then overriding it with your own values you are looking for issues. Either let the system do it's thing and be an out of site out of mind number generator or take full control of the id generation yourself. -- Adrian Klaver adrian.klaver@aklaver.com