CODEWITHSUNDEEP
sqlpostgresqlprimary-keyprimary-key-design
davidleongz
davidleongz

Reputation: 181

Create a Primary Key on a large table (6Million records) without locking the table in PostgreSQL

I want to create a Primary Key on a table with 6 Million records, but when I execute this:

ALTER TABLE schema_name.table_name ADD CONSTRAINT pkey_name PRIMARY KEY (field_pkey_name);

It's locking my table and alter table does not finish executing...

Upvotes: 0

Views: 2505

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247960

Try to do it in two steps:

CREATE UNIQUE INDEX CONCURRENTLY pkey_name
   ON schema_name.table_name (field_pkey_name);

ALTER TABLE schema_name.table_name
   ADD CONSTRAINT pkey_name PRIMARY KEY USING INDEX pkey_name;

It will still take a long time (even longer), but the table will not be locked for a long time.

Upvotes: 10

Related Questions