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
    Commented Feb 26, 2023 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
    Commented Mar 3, 2023 at 8:17

Your Answer

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

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