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
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';