Postgresql add primary key

I have a table with existing data. Is there a way to add a primary key without deleting and re-creating the table?

4 Answers 4

(Updated — Thanks to the people who commented)

Suppose you have a table named test1 , to which you want to add an auto-incrementing, primary-key id (surrogate) column. The following command should be sufficient in recent versions of PostgreSQL:

In old versions of PostgreSQL (prior to 8.x?) you had to do all the dirty work. The following sequence of commands should do the trick:

Again, in recent versions of Postgres this is roughly equivalent to the single command above.

This is all you need to:

1. Add the id column
2. Populate it with a sequence from 1 to count(*).
3. Set it as primary key / not null.

Credit is given to @resnyanskiy who gave this answer in a comment.

I had to first drop the pkey and then run this. ALTER TABLE

DROP CONSTRAINT

To use an identity column in v10,

For the difference between GENERATED BY DEFAULT and GENERATED ALWAYS, see https://www.cybertec-postgresql.com/en/sequences-gains-and-pitfalls/.

I landed here because I was looking for something like that too. In my case, I was copying the data from a set of staging tables with many columns into one table while also assigning row ids to the target table. Here is a variant of the above approaches that I used. I added the serial column at the end of my target table. That way I don’t have to have a placeholder for it in the Insert statement. Then a simple select * into the target table auto populated this column. Here are the two SQL statements that I used on PostgreSQL 9.6.4.

Summary: in this tutorial, we will show you what the primary key is and how to manage PostgreSQL primary key constraints through SQL statements.

A primary key is a column or a group of columns used to identify a row uniquely in a table.

You define primary keys through primary key constraints. Technically, a primary key constraint is the combination of a not-null constraint and a UNIQUE constraint.

A table can have one and only one primary key. It is a good practice to add a primary key to every table. When you add a primary key to a table, PostgreSQL creates a unique B-tree index on the column or a group of columns used to define the primary key.

Define primary key when creating the table

Normally, we add the primary key to a table when we define the table’s structure using CREATE TABLE statement.

Think Big Work Big

All Posts

• May 2014 (1)
• December 2011 (1)
• September 2011 (3)
• May 2011 (1)
• March 2011 (1)
• February 2011 (3)
• January 2011 (1)

This week I need to add a primary key on an existing table with huge data. But when I am going to add primary key get constrain related error. I solve this problem in following ways

• Add a column with type integer to the table
• Create a sequence
• Update the column table with sequence values
• Set the necessary column properties (e.g. default, not null, etc)

Add ‘id’ column as primary key in ‘cdindex’ table

Share this:

Like this:

Related

2 Comments

Easier way(Thanks to Brian_cs from #postgres)
alter table cdindex add column id serial primary key;

Thanks! So simple, yet it took me quite a while to find this solution on google.