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?