On Wed, Mar 01, 2006 at 04:11:07PM +1100, Shuying Wang wrote:
> Is it possible to drop and recreate an index on a column that's
> actually a primary key for a table in PostgreSQL 7.3 ? I couldn't
> figure that out from the documentation.
You can use ALTER TABLE to drop and later add the primary key
constraint. Is that what you mean?
http://www.postgresql.org/docs/7.3/interactive/sql-altertable.html
test=> CREATE TABLE foo (id integer PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE TABLE
test=> \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
Indexes: foo_pkey primary key btree (id)
test=> ALTER TABLE foo DROP CONSTRAINT foo_pkey;
ALTER TABLE
test=> \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
test=> ALTER TABLE foo ADD PRIMARY KEY (id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
ALTER TABLE
test=> \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
Indexes: foo_pkey primary key btree (id)
--
Michael Fuhr