Drop all indexes of a table w/o knowing the index names
am 09.11.2004 15:40:39 von giulioo
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
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Re: Drop all indexes of a table w/o knowing the index names
am 09.11.2004 16:30:25 von achill
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
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: Drop all indexes of a table w/o knowing the index names
am 09.11.2004 20:55:03 von giulioo
On Tue, 9 Nov 2004 17:30:25 +0200 (EET), Achilleus Mantzios
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend