Thread: Automatic PK values not added to new rows

Automatic PK values not added to new rows

From
Rich Shepard
Date:
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



Re: Automatic PK values not added to new rows

From
Adrian Klaver
Date:
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



Re: Automatic PK values not added to new rows

From
"David G. Johnston"
Date:
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.

Re: Automatic PK values not added to new rows

From
Rich Shepard
Date:
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



Re: Automatic PK values not added to new rows

From
Adrian Klaver
Date:
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



Re: Automatic PK values not added to new rows

From
Rich Shepard
Date:
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





Re: Automatic PK values not added to new rows

From
Rich Shepard
Date:
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



Re: Automatic PK values not added to new rows

From
Adrian Klaver
Date:
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



Re: Automatic PK values not added to new rows [RESOLVED]

From
Rich Shepard
Date:
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



Re: Automatic PK values not added to new rows

From
Thomas Kellerer
Date:
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.




Re: Automatic PK values not added to new rows

From
Rich Shepard
Date:
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



Re: Automatic PK values not added to new rows

From
Ron
Date:
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.



Re: Automatic PK values not added to new rows

From
Adrian Klaver
Date:
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