Change primary key to auto increment

Question

I have a table Player, and a primary key playerID which is character(7). I have some records in this table already, and also have some other tables that has playerID as a foreign key, and those tables also have some records already.

How can I set the playerID to auto-increment? After reading for a while I think that I should have done this from the beginning, but since I can't do that now, is there anyway I can do it?

For example, when I run this

ALTER TABLE player ADD COLUMN key_column BIGSERIAL PRIMARY KEY; 

it returns an error:

ERROR: multiple primary keys for table "player" are not allowed 

and if I drop the existing playerID, records in other tables that reference it will be dropped as well.

Is there a way to "change" the existing primary key playerID to auto increment?

Answer 1

I figure it out: just add an auto-increment default value to the playerID:

create sequence player_id_seq; alter table player alter playerid set default nextval('player_id_seq'); Select setval('player_id_seq', 2000051 ); --set to the highest current value of playerID 

Answer 2

DROP SCHEMA tmp CASCADE; CREATE SCHEMA tmp ; SET search_path=tmp;     -- create som data to play with CREATE TABLE bagger         ( player_id CHAR(6)         , tralala varchar         );    -- populate the table INSERT INTO bagger(player_id,tralala) SELECT gs::text, 'zzz_' || gs::text FROM generate_series(1,10) gs         ;  SELECT * FROM bagger;    --   -- create the sequence, change the datatype and bind it to the sequence   -- CREATE SEQUENCE player_id_seq; ALTER TABLE bagger         ALTER COLUMN player_id TYPE INTEGER USING player_id::integer         , ALTER COLUMN player_id SET NOT NULL         , ALTER COLUMN player_id SET DEFAULT nextval('player_id_seq')         ; ALTER SEQUENCE player_id_seq         OWNED BY bagger.player_id         ;    --    -- reset the sequence to containe the maximum occuring player_id in the table    -- SELECT setval('player_id_seq', mx.mx) FROM (SELECT MAX(player_id) AS mx FROM bagger) mx         ; SELECT * FROM bagger; \d bagger 

Output:

DROP SCHEMA CREATE SCHEMA SET CREATE TABLE INSERT 0 10  player_id | tralala  -----------+---------  1         | zzz_1  2         | zzz_2  3         | zzz_3  4         | zzz_4  5         | zzz_5  6         | zzz_6  7         | zzz_7  8         | zzz_8  9         | zzz_9  10        | zzz_10 (10 rows)  CREATE SEQUENCE ALTER TABLE   setval  --------      10 (1 row)   player_id | tralala  -----------+---------          1 | zzz_1          2 | zzz_2          3 | zzz_3          4 | zzz_4          5 | zzz_5          6 | zzz_6          7 | zzz_7          8 | zzz_8          9 | zzz_9         10 | zzz_10 (10 rows)                                   Table "tmp.bagger"   Column   |       Type        |                      Modifiers                       -----------+-------------------+-----------------------------------------------------  player_id | integer           | not null default nextval('player_id_seq'::regclass)  tralala   | character varying |