Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Z0rkK-0001SV-I3 for pgsql-sql@arkaria.postgresql.org; Fri, 05 Jun 2015 13:33:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Z0rkJ-0006dy-TN for pgsql-sql@arkaria.postgresql.org; Fri, 05 Jun 2015 13:33:15 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Z0rkI-0006dr-RF for pgsql-sql@postgresql.org; Fri, 05 Jun 2015 13:33:14 +0000 Received: from oldperseverance.encs.concordia.ca ([132.205.96.92]) by makus.postgresql.org with esmtp (Exim 4.84) (envelope-from ) id 1Z0rkB-0001GZ-Kg for pgsql-sql@postgresql.org; Fri, 05 Jun 2015 13:33:13 +0000 Received: from cacao.encs.concordia.ca (emilu@cacao.encs.concordia.ca [132.205.47.225]) by oldperseverance.encs.concordia.ca (envelope-from emilu@encs.concordia.ca) (8.13.7/8.13.7) with ESMTP id t55DX5n9006705; Fri, 5 Jun 2015 09:33:05 -0400 Message-ID: <5571A511.9020805@encs.concordia.ca> Date: Fri, 05 Jun 2015 09:33:05 -0400 From: Emi Lu Reply-To: emilu@encs.concordia.ca User-Agent: Mozilla/5.0 (X11; Linux i686 on x86_64; rv:31.0) Gecko/20100101 Thunderbird/31.6.0 MIME-Version: 1.0 To: "David G. Johnston" CC: "pgsql-sql@postgresql.org" Subject: Re: remove tablespace for primary key (*not* by drop/recreate constraint) References: <55707FAA.8060204@encs.concordia.ca> <55709A8A.7010005@encs.concordia.ca> In-Reply-To: Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit X-Scanned-By: MIMEDefang 2.58 on oldperseverance.encs.concordia.ca at 2015-06-05 09:33:06 EDT X-Pg-Spam-Score: -3.5 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org

to remove tablespace for primary key please?

For example, z1 (c1 text) with pk_z1 PRIMARY KEY (c1), tablespace "abc"

May I know how to remove tablespace(set tablespace to empty for z1)?

It doesn't make sense to "remove" a tablespace...the best you can do is change a table's (and its related indexes) tablespace

from one to another.

If "ALTER TABLE ... SET TABLESPACE ..." doesn't accomplish your goal you will need to explain yourself better.

Want to SET tablespace = '' for primary key but not table. Tried alter index ... set tablespace='', but empty does not work?

So, what you want to do is place the primary key index back onto the default tablespace while the table resides on a different tablespace?

Does this work?

ALTER INDEX ... SET TABLESPACE pg_default;

I think this is what I prefer to run. But it seems that schema owner does not have permission to run it.

"permission denied for tablespace pg_default"

Probably only postmaster can run it?

Thanks a lot!