RDBMS Information - PostgreSQL - How to find the linkage of schema and tablespace

This page provides the information on how to find the linkage of schema and tablespace in a particular Postgres server. The query will provide the results of all the tables and the corresponding tablespaces under which the schemas have been created.

 For more information on CAST Storage Service 2 (CSS2), please refer to CAST Storage Service.

After connecting to the postgres server in pgAdmin on which the linkage between schemas and their tablespaces are to be checked, run the following query:

select distinct schemaname, coalesce (tablespace, '<name of tablespace>') 
from pg_tables
union 
select distinct schemaname, coalesce (tablespace, '<name of tablespace>') 
from pg_indexes
order by 1

Most of the schemas that are created are under the 'default' tablespace, but the name of tablespace can be defined in the query.

For example, The above query when executed in pgAdmin for 'default' tablespace, will provide the following information from the connected postgres server:

select distinct schemaname, coalesce (tablespace, 'default') 
from pg_tables
union 
select distinct schemaname, coalesce (tablespace, 'default') 
from pg_indexes
order by 1

This query will list down all the tables and their tablespaces under the same postgres server.

Ticket  # 5912