Menu
I've got a script which copies table data from one DB to another, however the ID column is backed by a sequence so when a new record is inserted into the destination table the nextval(seq) is returning the wrong value.
I need to update the sequence so that it starts at the next available ID.
I want to do something like:
ALTER SEQUENCE seq_id RESTART WITH
(SELECT MAX(id) FROM tbl);
The above produces a syntax error however.
Can anyone suggest an alternative approach?
428
To reset a specific sequence through the SSMS GUI:In the SSMS GUI, navigate to Database Folder – [Database Name] – Programmability Folder – Sequences Folder. 4. Select the “Restart sequence” checkbox. You can enter the value you want to reset the sequence to, or keep the default value.
The only way to "reset" a sequence is to re-create it. As per the documentation (link below) you cannot reset a sequence's initial value after it's been set. Out of curiosity why do you want to do this? You'll never run out of sequence numbers if you start at 1 and increment by 1.
NEXTVAL is a function to get the next value from a sequence. Sequence is an object which returns ever-increasing numbers, different for each call, regardless of transactions etc. Each time you call NEXTVAL , you get a different number. This is mainly used to generate surrogate primary keys for you tables.
Sequences are integer values and can be of any data type that returns an integer. The data type cannot be changed by using the ALTER SEQUENCE statement. To change the data type, drop and create the sequence object.
DO $$
SELECT INTO m MAX(id) FROM tbl;
EXECUTE 'ALTER SEQUENCE seq_id RESTART WITH ' || m;
END$$;
or, better yet, see this question:
64
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
