Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2196F64FC5C for ; Tue, 21 Oct 2008 06:29:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 84493-07 for ; Tue, 21 Oct 2008 06:29:22 -0300 (ADT) X-Greylist: delayed 00:06:38.637506 by SQLgrey-1.7.6 Received: from renaissance-it.com (mail.renaissance-it.com [164.164.87.55]) by postgresql.org (Postfix) with ESMTP id 614C064FC5A for ; Tue, 21 Oct 2008 06:29:19 -0300 (ADT) Received: (qmail 14909 invoked by uid 109); 21 Oct 2008 09:20:01 -0000 Received: from 192.168.1.6 by sritmail (envelope-from , uid 112) with qmail-scanner-1.25st (perlscan: 1.25st. Clear:RC:1(192.168.1.6):. Processed in 0.01013 secs); 21 Oct 2008 09:20:01 -0000 Received: from unknown (HELO praveenkm) (praveen.k@[192.168.1.6]) (envelope-sender ) by renaissance-it.com (qmail-ldap-1.03) with SMTP for ; 21 Oct 2008 09:20:01 -0000 Message-ID: <01c001c9335f$06e991d0$274610ac@praveenkm> From: "praveen" To: "Campbell, Lance" , References: Subject: Re: Move tables to tablespace Date: Tue, 21 Oct 2008 14:55:58 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_01BD_01C9338D.208EBB00" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200810/143 X-Sequence-Number: 30599 This is a multi-part message in MIME format. ------=_NextPart_000_01BD_01C9338D.208EBB00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Yes you can do this using following commands. For tables:- select ' alter table '||tablename||' set tablespace ;' from pg_tables where schemaname=3D'' = and tableowner=3D'< mention table owner here >'; For index :- select ' alter index '||indexname||' set tablespace ;' from pg_indexes where schemaname=3D''; execute above commands in sql prompt and copy the output of these = command and paste again in sql prompt. ----- Original Message -----=20 From: Campbell, Lance=20 To: pgsql-admin@postgresql.org=20 Sent: Monday, October 20, 2008 7:40 PM Subject: [ADMIN] Move tables to tablespace I would like to have an SQL statement that would move all of the = tables and indexes found within a particular schema to a particular = tablespace. Is there a way I can do this? =20 Thanks, =20 Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu =20 ------=_NextPart_000_01BD_01C9338D.208EBB00 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Yes you can do this using following=20 commands.
For tables:-
select ' alter table '||tablename||' = set tablespace=20 <new tablespace name>;' from pg_tables where=20 schemaname=3D'<mention schema name here>' and=20 tableowner=3D'< mention table owner here = >';
 
For index :-
select ' alter = index '||indexname||' set=20 tablespace <new tablespace name>;' from = pg_indexes where=20 schemaname=3D'<mention schema name = here>';
 
execute above commands in sql prompt and copy the output of = these=20 command and paste again in sql prompt.
----- Original Message -----
From:=20 Campbell,=20 Lance
Sent: Monday, October 20, 2008 = 7:40=20 PM
Subject: [ADMIN] Move tables to = tablespace

I would like to have an SQL statement that would = move all=20 of the tables and indexes found within a particular schema to a = particular=20 tablespace.  Is there a way I can do this?

 

Thanks,

 

Lance=20 Campbell

Project=20 Manager/Software Architect

Web=20 Services at Public Affairs

University of=20 Illinois

217.333.0382

http://webservices.uiuc.edu

 

------=_NextPart_000_01BD_01C9338D.208EBB00--