Re: dropping an index

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