I have a table with an identity column defined by:
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
To begin with, I’ve inserted a few rows manually including the id column:
INSERT INTO things(id, …)
VALUES (…, …), (…, …), (…, …);
I now want to use auto-generated ids for the rest.
I’ve tried:
ALTER TABLE things ALTER COLUMN id RESTART WITH (SELECT max(id) FROM things);
but that doesn’t work.
I’ve even tried:
EXECUTE IMMEDIATE 'ALTER TABLE employees ALTER COLUMN id RESTART WITH ' || (SELECT MAX(id)+1 FROM employees);
I know I can restart with a constant value, but that’s not useful if I can’t predict the final id after the inserts.
Is there a way of dynamically restarting the identity column?
You can define a variable to pull the max(id) in a variable and use the variable in the EXECUTE IMMEDIATE and wrap it in a small anonymous block.
BEGIN
DECLARE max_id INT;
SET max_id = (SELECT NVL(MAX(id),0) FROM test); -- incase there is no row, NVL is added to return 0
EXECUTE IMMEDIATE 'ALTER TABLE test ALTER COLUMN id RESTART WITH ' || (max_id + 1);
END;
Data before running this block
ID NAME
1 Item 1
2 Item 2
3 Item 3
After running the block and running an insert
INSERT INTO test( name)
VALUES
( 'Item 4'),
( 'Item 5');
Outputs
ID NAME
1 Item 1
2 Item 2
3 Item 3
4 Item 4
5 Item 5