13

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

|improve this question
  • 1
    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
16

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;
|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
7
+25

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

|improve this answer
  • btw, "If ONLY is specified before the table name, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are altered." – Paolo Mar 12 '16 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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