Skip site navigation (1) Skip section navigation (2)

Re: Move tables to tablespace

From: "praveen" <praveen(dot)k(at)renaissance-it(dot)com>
To: "Campbell, Lance" <lance(at)illinois(dot)edu>,<pgsql-admin(at)postgresql(dot)org>
Subject: Re: Move tables to tablespace
Date: 2008-10-21 09:25:58
Message-ID: 01c001c9335f$06e991d0$274610ac@praveenkm (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
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(at)postgresql(dot)org 
  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?




  Lance Campbell

  Project Manager/Software Architect

  Web Services at Public Affairs

  University of Illinois



In response to

pgsql-admin by date

Next:From: Isabella GhiureaDate: 2008-10-21 17:45:09
Subject: oid2name
Previous:From: Tom LaneDate: 2008-10-20 22:50:24
Subject: Re: Privileges on sequences

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group