Select Page

How to add a Auto Increment Column to a PostgreSQL table

Unfortunately Postgresql does not have Auto Increment columns so instead we create a Sequence that will act as a counter

First we’ll need to create a sequence that we want to attch to our table

CREATE SEQUENCE mytable_primary_id_seq;

Then we need to assign the sequence to the column in our table that will act as the Autoincrement column

ALTER TABLE mytable
	    ALTER COLUMN primary_id
	        SET DEFAULT NEXTVAL('mytable_primary_id_seq');

No we need to fill in an auto increment value for all records that existed in the tablebefore we added a sequence to the table column primary_id

UPDATE mytable
	    SET primary_id = NEXTVAL('mytable_primary_id_seq');

Last Updated On April 07, 2018

Leave a reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.