Alter data type of a column to serial postgresql

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

See also [Adding 'serial' to existing column in Postgres](http://stackoverflow.com/questions/9490014/adding-serial-to-existing-column-in-postgres), and [a lot more](http://stackoverflow.com/search?q=%5Bpostgresql%5D+alter+serial)... — pozs, Feb 22 '17 at 08:37
A very similar question was asked and answered in [http://stackoverflow.com/questions/12591207/how-to-convert-integer-to-serial](http://stackoverflow.com/questions/12591207/how-to-convert-integer-to-serial) - would that help? — Colin Beckingham, Feb 22 '17 at 08:17

score 6 · Answer 1 · answered Feb 22 '17 at 12:46

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

1 Answers1