Re: remove tablespace for primary key (*not* by drop/recreate constraint)

From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Emi Lu
Sent: Friday, June 05, 2015 9:33 AM
To: David G. Johnston
Cc: pgsql-sql(at)postgresql(dot)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