snippetsqlMajor
How to promote an existing index to primary key in PostgreSQL
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:
I have created the index with:
But I do not know how to make it the primary key ...
UPDATE: The version of my server is 8.3.3
"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 "
Note, the index need to be unique of course for a primary key
ALTER TABLE .. ADD table_constraint_using_index" formNote, 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.