1

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?

1 Answer 1

2

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

2
  • 1
    note that the use of serial is discouraged in favour of standard compliant identity columns.
    – user1822
    Feb 26 at 19:40
  • The index is in the first column ,as your way, the index column will be at the last (most right-side) column,how to make it at the first line then?
    – showkey
    Mar 3 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.