How to set postgres pg_class reltablespace to the actual value, not 0?-CodePudding

I need pg_class.reltablespace to be the actual oid of the default tablespace instead of 0. For the existing and future new tables. I know this is per design but I need the actual oid for an automation.

Ex: This table is on the default namespace

# select relname,reltablespace from pg_class where relname = '<my table>'   ;
      relname       | reltablespace 
-------------------- ---------------
 <my table>         |             0

In my case it need to be "1663":

# SELECT oid, spcname FROM pg_tablespace where spcname = 'pg_default';
 oid  |  spcname   
------ ------------
 1663 | pg_default

Is there a config to change this behavior? Or some modification to the query that gets the tablespace oid

CodePudding user response：

If the relation's tablespace is 0, that means that the table resides in the default tablespace of the database:

SELECT t.oid::regclass,
       CASE WHEN t.reltablespace <> 0
            THEN t.reltablespace
            ELSE d.dattablespace
       END
FROM pg_class AS t
   CROSS JOIN pg_database AS d
WHERE d.datname = current_database()
  AND t.relname = 'mytable';