How to set auto increment primary key in PostgreSQL?

sql postgresql auto-increment
mkn picture mkn · Oct 10, 2011 · Viewed 462.5k times · Source

I have a table in PostgreSQL with many columns, and I want to add an auto increment primary key.

I tried to create a column called id of type BIGSERIAL but pgadmin responded with an error:

ERROR: sequence must have same owner as table it is linked to.

Does anyone know how to fix this issue? How do I add or create an auto-incrementing primary key in PostgreSQL without recreating the table?

Answer

A.H. picture A.H. · Oct 10, 2011

Try this command:

ALTER TABLE your_table ADD COLUMN key_column BIGSERIAL PRIMARY KEY;

Try it with the same DB-user as the one you have created the table.

Related questions

Reset auto increment counter in postgres

I would like to force the auto increment field of a table to some value, I tried with this: ALTER TABLE product AUTO_INCREMENT = 1453 AND ALTER SEQUENCE product RESTART WITH 1453; ERROR: relation "your_sequence_name" does not exist I'm new …

Read More
ERROR: permission denied for sequence cities_id_seq using Postgres

I'm new at postgres (and at database info systems all in all). I ran following sql script on my database: create table cities ( id serial primary key, name text not null ); create table reports ( id serial primary key, cityid integer …

Read More
Auto increment table column

Using Postgres, I'm trying to use AUTO_INCREMENT to number my primary key automatically in SQL. However, it gives me an error. CREATE TABLE Staff ( ID INTEGER NOT NULL AUTO_INCREMENT, Name VARCHAR(40) NOT NULL, PRIMARY KEY (ID) ); The error: ********** …

Read More