PostgreSQL - How to add auto increment primary key?

How to add increment primary key in an existing PostgreSQL table?

You have a PostgreSQL table and you want to add an auto increment primary key without recreating the table again.

1. Using Query Editor

Run this query:

ALTER TABLE table_name
	ADD COLUMN id SERIAL PRIMARY KEY;

For the older version of PostgreSQL that might not support the command above, you need use a custom sequence:

ALTER TABLE table_name
	ADD COLUMN id INTEGER;

CREATE SEQUENCE test_id_seq OWNED BY table_name.id;

ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq');

UPDATE
	table_name
SET
	id = nextval('test_id_seq');

2. Using TablePlus GUI

With TablePlus, you can do this from the database structure editor:

From the data view, click on the Structure button, or press Cmd + Ctrl + ]
Click the + Column button, or double click on the empty row to insert a new column
Name the column, use the serial datatype, and set NO value for the is_nullable field
Press Cmd + S to save the changes

Add auto increment key

New to TablePlus? It’s a modern, native tool with an elegant GUI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. 