How to change PRIMARY KEY of an existing PostgreSQL table?

change_primary_key.md

-- Firstly, remove PRIMARY KEY attribute of former PRIMARY KEY
ALTER TABLE <table_name> DROP CONSTRAINT <table_name>_pkey;

-- Then change column name of  your PRIMARY KEY and PRIMARY KEY candidates properly.
ALTER TABLE <table_name> RENAME COLUMN <primary_key_candidate> TO id;

-- Lastly set your new PRIMARY KEY
ALTER TABLE <table_name> ADD PRIMARY KEY (id);

worth noting that ALTER TABLE <table_name> DROP CONSTRAINT <table_name>_pkey; does not drop the not null constraint on the column.

@marcbachmann thank you! (didn't know postgres could reuse existing indices for newprimary keys, that's great)