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

  • 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 Dec 14, 2011 at 7:36
  • 1
    what is your PostgreSQL version?
    – filiprem
    Dec 14, 2011 at 10:41
  • I'm running 8.3.3
    – WildBill
    Dec 14, 2011 at 13:25

3 Answers 3


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 
  • 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, 2011 at 22:49
  • 1
    @ngramsky: USING INDEX is available since 9.1. So I guess you are not running the current version Dec 14, 2011 at 7:35
  • No, I am using 8.3.3 How would I go about doing that in 8.3.3?
    – WildBill
    Dec 15, 2011 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. Dec 15, 2011 at 15:17
  • 2
    @a_horse_with_no_name: you should make that an answer: your comment predates the other answer...
    – gbn
    Dec 16, 2011 at 19:10

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

  • 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, 2016 at 17:42

Using PostgreSQL9.5+, you can benefit from the unique index existence, as here:

CREATE TABLE foo (c1 integer, c2 varchar(20));
Time: 8.268 ms
INSERT INTO foo (c1, c2)
>  SELECT i, md5(random()::text)::varchar(20)
>  FROM generate_series(1, 1000000) AS i;
INSERT 0 1000000
Time: 1609.967 ms (00:01.610)
CREATE UNIQUE INDEX foo_idx01 ON foo(c1);
Time: 305.905 ms
ALTER TABLE foo add constraint foo_pk primary key using index foo_idx01 ;
NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "foo_idx01" to "foo_pk"
Time: 79.664 ms

The alter table statement is quicker than the create unique index itself

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.