How to modify a primary key index to become serial?

Question

I have a table that has a primary key which I'd like to become SERIAL (auto increment).

How to do that?

=> select * from information_schema.table_constraints where table_name='role';
 constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | constraint_type | is_deferrable | initially_deferred | enforced 
--------------------+-------------------+-----------------+---------------+--------------+------------+-----------------+---------------+--------------------+----------
 XXXXXXXX           | public            | role_pkey       | XXXXXXXX      | public       | role       | PRIMARY KEY     | NO            | NO                 | YES

Answer 1

The fact that it's a primary key isn't relevant to the solution. You would need to determine what the highest value already used in the column that you want to use a sequence, create a sequence that starts at a number higher than that, and then set the ownership of that sequence to that column.

For example, if you have the following table:

CREATE TABLE mytable (
  id INT NOT NULL PRIMARY KEY,
  content TEXT
);

Where the highest value of id is 2500, you would do the following:

CREATE SEQUENCE mytable_id_seq START 2501 OWNED BY mytable.id;

ALTER TABLE mytable ALTER COLUMN id SET DEFAULT nextval('mytable_id_seq');

Then, when you insert into the table (without specifying the id column, or using the keyword DEFAULT), it will select the next value from that sequence.

Setting the owner of the sequence to be the table column means that the sequence will get dropped with the table if you ever drop it, rather than leaving an orphaned sequence.