Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to change PRIMARY KEY of an existing PostgreSQL table?
-- 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);
@jamilnyc

This comment has been minimized.

Copy link

@jamilnyc jamilnyc commented Jun 18, 2016

Thanks! This was really useful.

@micklinISgood

This comment has been minimized.

Copy link

@micklinISgood micklinISgood commented Nov 7, 2016

Alter table <table_name> ALTER COLUMN <COLUMN_name> set not null

@dhhiep

This comment has been minimized.

Copy link

@dhhiep dhhiep commented Nov 29, 2016

Thanks Bro

@oldigor

This comment has been minimized.

Copy link

@oldigor oldigor commented Feb 19, 2017

Thanks.

@hello-josh

This comment has been minimized.

Copy link

@hello-josh hello-josh commented Mar 31, 2017

fantastic!

@geekmichael

This comment has been minimized.

Copy link

@geekmichael geekmichael commented Apr 6, 2017

It's good note.

@brunojppb

This comment has been minimized.

Copy link

@brunojppb brunojppb commented Apr 10, 2017

saved my day! thanks

@jadams74

This comment has been minimized.

Copy link

@jadams74 jadams74 commented Apr 13, 2017

👍

@grantrobertsmith

This comment has been minimized.

Copy link

@grantrobertsmith grantrobertsmith commented May 11, 2017

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.

@samartioli

This comment has been minimized.

Copy link

@samartioli samartioli commented Aug 28, 2017

Should you reindex after changing the primary key?

@prajaktabanne

This comment has been minimized.

Copy link

@prajaktabanne prajaktabanne commented Aug 31, 2017

thanks

@Karizu

This comment has been minimized.

Copy link

@Karizu Karizu commented Sep 25, 2017

Thanks bro!

@amineds

This comment has been minimized.

Copy link

@amineds amineds commented Nov 15, 2017

helpful ! cheers

@MikilchenkoMarina

This comment has been minimized.

Copy link

@MikilchenkoMarina MikilchenkoMarina commented Dec 6, 2017

thank you !!!

@oblack

This comment has been minimized.

Copy link

@oblack oblack commented Mar 13, 2018

Thanks

@kokovych

This comment has been minimized.

Copy link

@kokovych kokovych commented Aug 2, 2018

Great job 👍

@kaggwachristopher

This comment has been minimized.

Copy link

@kaggwachristopher kaggwachristopher commented Sep 9, 2018

Thanks alot

@saintbyte

This comment has been minimized.

Copy link

@saintbyte saintbyte commented Oct 1, 2018

Спасибо

@vwalker04

This comment has been minimized.

Copy link

@vwalker04 vwalker04 commented Feb 2, 2019

beautiful! thanks!

@naldi28

This comment has been minimized.

Copy link

@naldi28 naldi28 commented Apr 5, 2019

Thank you so much. It's work

@elomariAchraf

This comment has been minimized.

Copy link

@elomariAchraf elomariAchraf commented Oct 15, 2019

thanks alot

@S00ahKim

This comment has been minimized.

Copy link

@S00ahKim S00ahKim commented Feb 18, 2020

thank you!

@mertyertugrul

This comment has been minimized.

Copy link

@mertyertugrul mertyertugrul commented Apr 23, 2020

cheers, thank you.

@anoop-sharma

This comment has been minimized.

Copy link

@anoop-sharma anoop-sharma commented Jul 8, 2020

Thank you for good explanation.

@jwatte

This comment has been minimized.

Copy link

@jwatte jwatte commented Jan 16, 2021

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

@venriq

This comment has been minimized.

Copy link

@venriq venriq commented Jan 25, 2021

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment