HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMajor

How to promote an existing index to primary key in PostgreSQL

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
promotepostgresqlprimaryhowexistingindexkey

Problem

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

Solution

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;

Code Snippets

ALTER TABLE my_table 
    ADD CONSTRAINT PK_my_table PRIMARY KEY USING INDEX my_index;

Context

StackExchange Database Administrators Q#8814, answer score: 34

Revisions (0)

No revisions yet.