the default value of the primarykey field must be : nextval('your_sequence_name'::regclass)
OK. I’m converting an existing database. I must specify id to retain integrity. There are ‘holes’ in the sequence because of previously deleted records. What would be wrong with creating a function to check for max(id) and use the function as the default value? Would that slow down inserts due the query?
For the conversion you don’t want and don’t need the autoincrement. Ok.
But for “normal” use you could take the autoincrement of bigserials (automatically done without writing the id). Why do you want a function do do that?
[quote=305606:@Jean-Yves Pochez]you must import your tables with the values of your ID
then after the import process, set the sequences values to max(id) +1 and then the autoincrement process can start on its own[/quote]
I’ve not been able the set the sequence start using a query, only an actual integer. I tried this:
'this works
ALTER SEQUENCE categories_id_seq START 10
'but not this
ALTER SEQUENCE categories_id_seq START SELECT MAX(ID)+1 FROM Categories
'or this
ALTER SEQUENCE categories_id_seq START (SELECT MAX(ID)+1 FROM Categories)
I do it with xojo.
read the max id value from the first database
make a string with the desired value as a query to the second database
rs1 = db1.sqlselect "select max(id)+1 from categories"
mymaxid = rs1.idxfield(1).integervalue
db2.sqlexecute "alter sequence categories_id_seq start "+str(mymaxid)