Thread: Drop all indexes of a table w/o knowing the index names

7.4.6 on Linux.

I need a way to drop all indexes of a table without knowing the names of the
indexes.

Say I have a table

table1index1index2index3

I don't want to do

drop index1;
drop index2;
drop index3;

but I want

drop <all indexes of table table1>

is this possible? I looked in the manual at pg_index, but couldn't build an
sql string to do it.

Thanks

-- 
giulioo@pobox.com

O Giulio Orsero έγραψε στις Nov 9, 2004 :

> 7.4.6 on Linux.
> 
> I need a way to drop all indexes of a table without knowing the names of the
> indexes.
> 
> Say I have a table
> 
> table1
>     index1
>     index2
>     index3
> 
> I don't want to do
> 
> drop index1;
> drop index2;
> drop index3;
> 
> but I want
> 
> drop <all indexes of table table1>

if your are using default namespace (schema)
% tcsh

% foreach i ( `psql -t -q -c "SELECT ci.relname from pg_index i,pg_class 
ci,pg_class ct where i.indexrelid=ci.oid and i.indrelid=ct.oid and 
ct.relname='YOUR_TABLE_HERE'"` )
foreach? psql -c "drop index $i" 
foreach? end

> 
> is this possible? I looked in the manual at pg_index, but couldn't build an
> sql string to do it.
> 
> Thanks
> 
> 

-- 
-Achilleus

On Tue, 9 Nov 2004 17:30:25 +0200 (EET), Achilleus Mantzios
<achill@matrix.gatewaynet.com> wrote:

>O Giulio Orsero Ýãñáøå óôéò Nov 9, 2004 :
>> I need a way to drop all indexes of a table without knowing the names of the
>> indexes.

>% foreach i ( `psql -t -q -c "SELECT ci.relname from pg_index i,pg_class 
>ci,pg_class ct where i.indexrelid=ci.oid and i.indrelid=ct.oid and 
>ct.relname='YOUR_TABLE_HERE'"` )
>foreach? psql -c "drop index $i" 
>foreach? end

Ok, worked as expected.

thanks

-- 
giulioo@pobox.com