Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?

[Ravi Tammineni <rtammineni@xxxxxxxxxxxxxxxxxxxxx>]

Hi Samed,

 

Thanks for the response.

 

But my question is, I have to add a new id column to the existing table, populate the data and then convert that column into primary key column. I am running the following command but its taking forever.

 

ALTER TABLE tblclinchecklog ADD COLUMN id serial primary key;

 

Is there a better way? Like

 

1>     Add column

2>     Create sequence

3>     Update the table

4>     Create index

5>     Convert the index into pk index

 

Thanks

ravi

 

From: Samed YILDIRIM [mailto:samed@xxxxxxxxxx]
Sent: Thursday, August 18, 2016 2:05 PM
To: Ravi Tammineni <rtammineni@xxxxxxxxxxxxxxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: [ADMIN] What is the best way to create Primary Key on a large table in Postgresql 9.5?

 

Hi Ravi,

You can use CREATE INDEX with CONCURRENTLY keyword. It will not lock your table during the process.

Best regards.



İyi çalışmalar.
Samed YILDIRIM


19.08.2016, 00:01, "Ravi Tammineni" <rtammineni@xxxxxxxxxxxxxxxxxxxxx>:

 I have to create a primary key on a large table (~100Million records). What is the best and fastest way to create pkey? This column is a sequence column and i don't want to lock the table because this is highly transactional database.

 Thanks

 ravi