Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
1  
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 
    ADD CONSTRAINT PK_my_table PRIMARY KEY USING INDEX my_index;
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
1  
@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
5  
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
2  
@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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.