1

I have a table like the following:

create table my_table
(
    "ID"            serial not null,
    "Name"          varchar(500)
);

Without changing the property of the table, is there any clean way to insert a new row to this table (already has values, so "ID" does not start from 1) such that the column "ID" keeps incrementing? Currently, if I insert a new row Charlie (without specifying the ID column) to the table:

ID    |    Name
----------------
1     |    Alice
2     |    Bob

It will become

ID    |    Name
----------------
1     |    Alice
2     |    Bob
1     |    Charlie

Then as I keep inserting, the serial counter keeps incrementing. Is there any way I can start from 3? I've tried currval(), nextval() and lastval(), but they all seem to start from 1 even though I have a serial column with values already. If I have to end up changing the properties of this table, any way I can change it such that I won't have to worry about the serial column when I insert new rows?

cxc
  • 201
  • 2
  • 10
  • 1
    Does this answer your question? [Postgres manually alter sequence](https://stackoverflow.com/questions/8745051/postgres-manually-alter-sequence) – Mike Organek Oct 01 '20 at 17:32
  • @MikeOrganek Thanks for the suggestion but not quite hmm so what if I don't know the last value? Obviously, I can do a select first and count, but just wondering if there is any clean/proper way to do it. – cxc Oct 01 '20 at 17:59
  • 1
    https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync –  Oct 01 '20 at 18:49
  • @a_horse_with_no_name Thanks a lot! I think I am going with this solution! – cxc Oct 01 '20 at 19:01

0 Answers0