Alter data type of a column to serial

Unveiling PostgreSQL serial

Inside PostgreSQL, SERIAL is not a standalone guardian, but more like a shortcut that says "hey, let's make this integer column special with an auto-incrementing capability".

When you give birth to a new table, declaring a column SERIAL makes PostgreSQL busy — automatically creating a sequence that injects life into it, making it grow by itself with each new entry.

However, when an existing column undergoes puberty, you can't just miraculously alter it to SERIAL. You need a more explicit series of spells that involve creating a sequence, setting next value, and linking it. Voila!

A quick heads-up

Our beautiful PostgreSQL world is not without its quirks and gotchas:

1. Conduct the setval ritual after inserting the sequence to ensure it starts from your preferred base camp.
2. Deleting a column or table? Make sure to clean up your sequences as well, else they might turn into orphaned zombies. OWNED BY to the rescue!
3. Always be nice to concurrency — when modifying sequences, ensure proper locking to avoid collisions.

More magic potions

Assigning sequence ownership

-- "I, sequence, belong only to thee!"
ALTER SEQUENCE your_table_col_seq OWNED BY your_table.your_column;


Using bigserial

For a giant range use bigserial:

-- "Size does matter!"
CREATE SEQUENCE your_table_col_bigseq OWNED BY your_table.your_bigint_column;
ALTER TABLE your_table ALTER COLUMN your_bigint_column SET DEFAULT nextval('your_table_col_bigseq');
SELECT setval('your_table_col_bigseq', COALESCE(MAX(your_bigint_column), 0) + 1) FROM your_table;


Reincarnating a sequence

To restart a sequence from a new birth:

-- "I believe in rebirth!"
SELECT setval('your_table_col_seq', 100);


Advanced sequence maneuvering

Serial or sequence

Choosing between SERIAL and creating a custom sequence boils down to your specific needs. If you require controlled increments, tables sharing a sequence, or non-standard increments, use CREATE SEQUENCE.

Crafting custom sequence

A handful of neat options are available with CREATE SEQUENCE such as INCREMENT BY, MINVALUE, and MAXVALUE:

-- "Let's step up the game, shall we?"
CREATE SEQUENCE your_table_col_custom_seq INCREMENT BY 10 START WITH 100 MINVALUE 100;


Sharing sequences

Sharing is caring! For a shared counter between tables, use a common sequence:

-- "One sequence to govern them all!"
CREATE SEQUENCE shared_seq;
ALTER TABLE table_one ALTER COLUMN one_id SET DEFAULT nextval('shared_seq');
ALTER TABLE table_two ALTER COLUMN two_id SET DEFAULT nextval('shared_seq');


Sequence alignment

A periodic re-checking of the sequence health is crucial. Sequences may disarrange over time - with scripts forgetting or ignoring to use them, leading to erratic gaps or overlaps.