Change primary key to auto increment

I don't think you can have 2 primary keys in one table, and because playerID data type is character(7) i don't think you can change it to auto increment.

So i believe you would have to remove the primary key constraint on playerID if you want to be able to add a new primary key.

Since you already have data on your table and you use playerID as a foreign key in other tables, i would advise you to duplicate your player table and test these changes on your second table to avoid corrupting your data.

But before you try all this, make sure that you are trying to make this changes using the same db-user that created the tables you are changing

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 | 

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