Yes you can do this using following commands.
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
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?
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
In response to
pgsql-admin by date
|Next:||From: Isabella Ghiurea||Date: 2008-10-21 17:45:09|
|Subject: oid2name |
|Previous:||From: Tom Lane||Date: 2008-10-20 22:50:24|
|Subject: Re: Privileges on sequences |