How To Alter primary Key default Value?

Question

ALTER TABLE hr_leave ALTER COLUMN id DEFAULT (select Max(id)+2)

How can I write this query with a prober way to alter primary key default value equal max id +2

https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync — , Aug 03 '21 at 14:43
@AhmedElsayed glad to hear. Consider accepting my answer, so that other users know the issue has been solved: https://stackoverflow.com/help/someone-answers — Jim Jones, Aug 04 '21 at 14:54

Jim Jones · Answer 1 · 2021-08-03T15:00:19.543

First create a SEQUENCE:

CREATE SEQUENCE myseq START WITH 1 INCREMENT BY 2;

In case table hr_leave already contains records and you wish to change the initial value of your new sequence to the max id value of hr_leave do the following:

SELECT setval('myseq', (SELECT max(id) FROM hr_leave));

.. then finally change the column id to use the sequence myseq:

ALTER TABLE hr_leave ALTER COLUMN id SET DEFAULT nextval('myseq');

Demo: db<>fiddle

How To Alter primary Key default Value?

1 Answers1