How to add a Auto Increment Column to a PostgreSQL table

H

How Can We Help?

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');

About the author

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.

About Author

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.

Follow Me