How can set the column as a primary key with serial type?

Question

In my postgresql:

select index from sample;
index
---------
1
2
3
1
2

How can set the column index as a primary key with serial type for table sample?And the selected value should be as below:

select index from sample;
index
---------
1
2
3
4
5

I have tried to add it as primary key:

alter table sample add primary key (index);
ERROR:  could not create unique index "sample_pkey"
DETAIL:  Key (index)=(1) is duplicated.
Time: 1.580 ms

How can delete all the value in field index ,and assign a continuous range or serial value to it?

Answer 1

Follow these Steps:

1. Add new column sort_id in sample table.

   alter table sample add column sort_id serial;

2. Drop column index from sample table.

   alter table sample drop column index;

3. Rename column sort_id to index in sample table

   alter table sample rename column sort_id to index;

4. Add Primary Key.

   alter table sample add primary key(index);