Re: Move tables to tablespace

["praveen" <praveen.k@xxxxxxxxxxxxxxxxxx>]

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 -----

From: Campbell, Lance

To: pgsql-admin@xxxxxxxxxxxxxx

Sent: Monday, October 20, 2008 7:40 PM

Subject: 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