Alter Postgres Tablespace [closed]

I can’t seem to create a table in a particular tablespace. For instance, when I do:

CREATE TABLE myschema.mytable (rc_id integer NOT NULL) TABLESPACE my_space;

and then I do:

select * from pg_tables where schemaname ='myschema' limit 10;

…the tablespace is blank. The tablespace shows up in PgAdmin as pg_default. What do I need to change to create the non-default tablespace?

By definition, “tablespace” field of pg_tables says “Name of tablespace containing table (null if default for database)”. To get an exact tablespace name for a table or a table list you should first get the default tablespace for particular database. The query below lists all tables in schema myschema

WITH tbl_spc AS (
  --Get default tablespace for the database
  SELECT ts.spcname AS spcname
    FROM pg_tablespace ts 
    JOIN pg_database db ON db.dattablespace = ts.oid
    WHERE db.datname = current_database()
)
SELECT
  t.schemaname,
  t.tablename,
  --if null then get default tablespace
  COALESCE(t.tablespace, ts.spcname) AS tablespace
FROM pg_tables t, tbl_spc ts
WHERE
  t.schemaname = 'myschema'