Postgres Pro
Downloads
Home   >   mailing lists

Thread: Move tables to tablespace

Move tables to tablespace

From
"Campbell, Lance"
Date:

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?

 

Thanks,

 

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

 

Re: Move tables to tablespace

From
"Scott Marlowe"
Date:
On Mon, Oct 20, 2008 at 8:10 AM, Campbell, Lance <lance@illinois.edu> wrote:
> 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?

I think you'll have to write a script (either external or in plpgsql)
to do this.

Re: Move tables to tablespace

From
"praveen"
Date:
Yes you can do this using following commands.
For tables:-
select ' alter table '||tablename||' set tablespace <new tablespace name>;' from pg_tables where schemaname='<mention schema name here>' and tableowner='< mention table owner here >';
 
For index :-
select ' alter index '||indexname||' set tablespace <new tablespace name>;' from pg_indexes where schemaname='<mention schema name here>';
 
execute above commands in sql prompt and copy the output of these command and paste again in sql prompt.
----- Original Message -----
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?

 

Thanks,

 

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu