How to promote an existing index to primary key in PostgreSQL

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

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;

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

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

CREATE TABLE foo (c1 integer, c2 varchar(20));
​
CREATE TABLE
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);
​
CREATE INDEX
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"
ALTER TABLE
Time: 79.664 ms

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