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);

ty

Thanks

Thanks a lot !!

As @venriq mentioned, creating the new index first could be better.
Postgres could reuse other indexes during creation, if there's already one with the same columns.

I'm using that one to switch a multi column index:

ALTER TABLE <table_name> RENAME CONSTRAINT <table_name>_pkey TO <table_name>_pkeyold;
CREATE UNIQUE INDEX <table_name>_pkey ON <table_name> (id, namespace);
ALTER TABLE <table_name> DROP CONSTRAINT <table_name>_pkeyold;
ALTER TABLE <table_name> ADD PRIMARY KEY USING INDEX <table_name>_pkey;

thank you

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)

thx, man! it's cool

Thank you !

Save the process bro