Take the 2-minute tour ×

I know how to make a primary key within a table, but how do I make an existing index a primary key? I'm trying to copy an existing table from one database to another. When I show the table, the index at the bottom is in this form:

"my_index" PRIMARY KEY, btree (column1, column2)

I have created the index with:

CREATE INDEX my_index ON my_table (column1, column2)

But I do not know how to make it the primary key ...

UPDATE: The version of my server is 8.3.3

share|improve this question
If my_index is shown as the name of the primary key then you already have an index. There is no PK without an (unique) index in Postgres –  a_horse_with_no_name Dec 14 '11 at 7:36
what is your PostgreSQL version? –  filiprem Dec 14 '11 at 10:41
I'm running 8.3.3 –  WildBill Dec 14 '11 at 13:25

2 Answers 2

I don't think its possible to convert an index into a primary key in that version of postgresql.

I would just drop the existing index and create the primary key using the columns you specified.:

DROP INDEX my_index;
ALTER TABLE ONLY my_table ADD CONSTRAINT pk_my_table PRIMARY KEY(column1,column2);

Worked in 7.4 and 8.4

share|improve this answer

You'd use ALTER TABLE to add the primary key constraint. In Postgres you can "promote" an index using the "ALTER TABLE .. ADD table_constraint_using_index" form

Note, the index need to be unique of course for a primary key

ALTER TABLE my_table 
share|improve this answer
That did not work for me. What is PK_my_table? I assume this is a label and I append 'PK_' to the name of my table? I got a syntax error saying "ERROR: syntax error at or near "USING" at character 83" –  WildBill Dec 6 '11 at 22:49
@ngramsky: USING INDEX is available since 9.1. So I guess you are not running the current version –  a_horse_with_no_name Dec 14 '11 at 7:35
No, I am using 8.3.3 How would I go about doing that in 8.3.3? –  WildBill Dec 15 '11 at 0:08
The only way to do it in that ancient version is to drop the index and add a new pk constraint. –  a_horse_with_no_name Dec 15 '11 at 15:17
@a_horse_with_no_name: you should make that an answer: your comment predates the other answer... –  gbn Dec 16 '11 at 19:10

Your Answer


