Postgres Pro
Downloads
Home   >   mailing lists

Re: remove tablespace for primary key (*not* by drop/recreate constraint) - Mailing list pgsql-sql

From Igor Neyman
Subject Re: remove tablespace for primary key (*not* by drop/recreate constraint)
Date
Msg-id A76B25F2823E954C9E45E32FA49D70ECCD45F1F1@mail.corp.perceptron.com
Whole thread Raw
In response to Re: remove tablespace for primary key (*not* by drop/recreate constraint)  (Igor Neyman <ineyman@perceptron.com>)
Responses Re: remove tablespace for primary key (*not* by drop/recreate constraint)  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: remove tablespace for primary key (*not* by drop/recreate constraint)  (Emi Lu <emilu@encs.concordia.ca>)
List pgsql-sql
Tree view

 

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Igor Neyman
Sent: Friday, June 05, 2015 9:48 AM
To: emilu@encs.concordia.ca; David G. Johnston
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] remove tablespace for primary key (*not* by drop/recreate constraint)

 

 

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Emi Lu
Sent: Friday, June 05, 2015 9:33 AM
To: David G. Johnston
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] remove tablespace for primary key (*not* by drop/recreate constraint)

 

 

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!

 

Use:

 

GRANT USAGE ON SCHEMA…

 

 

Regards,

Igor Neyman

 

Actually, you probably need:

 

GRANT CREATE ON SCHEMA…

 

 

Regards,

Igor Neyman

 

pgsql-sql by date:

Previous
From: Igor Neyman
Date:
Subject: Re: remove tablespace for primary key (*not* by drop/recreate constraint)
Next
From: "David G. Johnston"
Date:
Subject: Re: remove tablespace for primary key (*not* by drop/recreate constraint)