Thread: dropping an index

Hi,

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.

--Shuying

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

Michael Fuhr <mike@fuhr.org> writes:
> 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?

I think what's probably wanted here is REINDEX ...

            regards, tom lane