PK issue: serial sequence needs updating

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

PK issue: serial sequence needs updating

Rich Shepard
Running postgresql-12.2-x86_64-1_SBo on Slackware-14.2/x86_64.

My restructed business database has populated tables, but the primary keys
are not aware of the current maximum number since the table rows were added
external to postgres and read in using psql.

For example, I'm trying to add a new person to the people table which
currently has 484 rows. The insert into statement provides all columns but
the first, the PK (person_nbr) which is an int with an associated
people_person_nbr_seq. Passing the insert statement to psql is rejected
because there's already a person_nbr=1.

How do I inform the sequence that nextval should be 485? Or, do I drop that
sequence and restore it so it reads all existing table rows and their PKs?

If I didn't clearly explain myself, ask and I'll try again. :-)

TIA,

Rich




Reply | Threaded
Open this post in threaded view
|

Re: PK issue: serial sequence needs updating

Adrian Klaver-4
On 11/17/20 3:46 PM, Rich Shepard wrote:

> Running postgresql-12.2-x86_64-1_SBo on Slackware-14.2/x86_64.
>
> My restructed business database has populated tables, but the primary keys
> are not aware of the current maximum number since the table rows were added
> external to postgres and read in using psql.
>
> For example, I'm trying to add a new person to the people table which
> currently has 484 rows. The insert into statement provides all columns but
> the first, the PK (person_nbr) which is an int with an associated
> people_person_nbr_seq. Passing the insert statement to psql is rejected
> because there's already a person_nbr=1.
>
> How do I inform the sequence that nextval should be 485? Or, do I drop that
> sequence and restore it so it reads all existing table rows and their PKs?
>
> If I didn't clearly explain myself, ask and I'll try again. :-)

https://www.postgresql.org/docs/12/sql-altersequence.html


ALTER SEQUENCE people_person_nbr_seq RESTART 485;

>
> TIA,
>
> Rich
>
>
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: PK issue: serial sequence needs updating [RESOLVED]

Rich Shepard
On Tue, 17 Nov 2020, Adrian Klaver wrote:

> https://www.postgresql.org/docs/12/sql-altersequence.html
> ALTER SEQUENCE people_person_nbr_seq RESTART 485;

Thanks, Adrian. I missed that page.

Regards,

Rich