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

 z1 (c1 text) with pk_z1 PRIMARY KEY (c1), tablespace "abc"
how to remove tablespace(set tablespace to empty for z1)?

ALTER INDEX ... SET TABLESPACE pg_default;

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?

 

GRANT USAGE ON SCHEMA…  GRANT CREATE ON SCHEMA…

schema owner already have full control for the whole schema, this username can create/drop tables/indexs, even drop schema. I think the permission is related to the pg_default - the tablespace. For example, there are 3 tablespaces: pg_default, abc, test (is the one used by table z1)

. alter index pk_z1 set tablespace abc; (success)
. alter index pk_z1 set tablespace test (permission denied)
. alter index pk_z1 set tablespace pg_default (permission denied)