25

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

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
WildBill
  • 525
  • 1
  • 7
  • 15

3 Answers3

34

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;
gbn
  • 69,809
  • 8
  • 163
  • 243
  • 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 06 '11 at 22:49
  • 1
    @ngramsky: USING INDEX is available since 9.1. So I guess you are not running the current version –  Dec 14 '11 at 07:35
  • No, I am using 8.3.3 How would I go about doing that in 8.3.3? – WildBill Dec 15 '11 at 00: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 '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
  • If you currently don't have a unique index, I recommend this answer: https://dba.stackexchange.com/a/188541/121426 – Alphaaa Aug 14 '20 at 10:34
  • Heads up, this solution is viable for a unique index, but not for a unique constraint, since you can't add the primary key constraint while the unique constraint exists, and you can't drop the unique constraint without also dropping the index it manages.

    Also-- see Thomas B's answer below for more details on the same idea as this answer.

     – Gabe Kopley Sep 16 '20 at 01:05
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

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Craig Efrein
  • 9,640
  • 12
  • 58
  • 96
  • 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
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

Thomas B
  • 103
  • 5