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

fantastic!

It's good note.

saved my day! thanks

👍

It's important to note that if the primary key is used as a foreign key constraint in other tables, you'll have to include the keyword CASCADE at the end of the DROP CONSTRAINT command. Subsequently, you will also need to individually recreate the foreign keys in the other tables.

Should you reindex after changing the primary key?

thanks

Thanks bro!

helpful ! cheers

thank you !!!

Thanks

Great job 👍

Thanks alot

Спасибо

beautiful! thanks!

Thank you so much. It's work

thanks alot

thank you!

cheers, thank you.

Thank you for good explanation.

If you don't need to rename the columns, you can just do this in a single statement:

ALTER TABLE mytable
DROP CONSTRAINT mytable_pkey,
ADD PRIMARY KEY(lastname, firstname, birthdate); -- or whatever

I'd recommend having some Unique Index backing up the Primary Key before deleting it.
Imagine querying a large table on Production, with millions of rows where the queries depend on the Primary Key (PK) to run efficiently, but suddenly the PK is gone. The queries will pay a high penalty, considering that recreating the PK will take some time depending on the table's size.
Also, always consider creating indices with the CONCURRENTLY option, so the table is not locked.

Thanks!

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)