How to promote an existing index to primary key in PostgreSQL

Question

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

Answer 1

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;

Answer 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

Answer 3

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