How to insert rows with the serial column catching up from the largest value in the table in Postgres

138 Views Asked by At

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?

0

There are 0 best solutions below