How to add a Auto Increment Column to a PostgreSQL table

Written by Ian Carnaghan · 24 sec read >

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 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
Written by Ian Carnaghan
I am a software developer and online educator who likes to keep up with all the latest in technology. I also manage cloud infrastructure, continuous monitoring, DevOps processes, security, and continuous integration and deployment. In my spare time I teach undergraduate classes in web development. Profile

Leave a Reply

Notify of