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

[Don't use serial](https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_serial) — , Aug 04 '21 at 15:28
More information needed: 1) Postgres version? 2) What is the current type of the PK? 3) Does the current column have values and do they need to be retained? 4) If they need to be retained what is the range of values? If the Postgres version is new enough you probably want to look at [Identity](https://www.postgresql.org/docs/current/sql-altertable.html). — Adrian Klaver, Aug 04 '21 at 15:28

Jim Jones · Answer 1 · 2021-08-04T15:43:37.173

1

The standard way to do this is to use a sequence.

Create a sequence and set the starting value to the last value of your primary key

CREATE SEQUENCE t_seq INCREMENT BY 1;
SELECT setval('t_seq', (SELECT max(id) FROM role));

.. and finally you attach the sequence to your table

ALTER TABLE public.role ALTER COLUMN id SET DEFAULT nextval('t_seq');

Demo: db<>fiddle

edited Aug 04 '21 at 15:43

answered Aug 04 '21 at 15:33

Jim Jones

18,404
3
35
44

1

Hey thanks! Should I name "t_seq" according to my table and column or is it some kind of dumb name? – Stéphane de Luca Aug 04 '21 at 16:00
@StéphanedeLuca this is just a dummy name for the sequence.. it can be anything :) – Jim Jones Aug 04 '21 at 16:09

score 1 · Accepted Answer · answered Aug 04 '21 at 15:35

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.

How to modify a primary key index to become serial?

2 Answers2