Alter data type of a column to serial postgresql [duplicate]

Question

How do i do to alter a table column in serial. This column has already datas in it and i don't want to lost them.. I have tried this:

ALTER TABLE tbl_user ADD COLUMN usr_id SERIAL PRIMARY KEY;

i get this error

ERROR: column "usr_id" of relation "tbl_user" already exists ********** Erreur **********

i tried this also:

UPDATE tbl_user SET usr_id = nextval('seq_user') WHERE usr_id IS NULL;

Query returned successfully: 0 rows affected, 71 msec execution time.

the query turn succesfull but doesn't change the column type

Answer 1

serial is a pseudo type. It is indeed an integer so you already have half of it. What is missing is to assign the sequence as the default value:

alter table tbl_user 
alter column usr_id set default nextval('seq_user')

But before that it is necessary to find the biggest value of usr_id and set it as the minimum sequence value + 1:

select max(usr_id) from tbl_user;

alter sequence seq_user minvalue 1001