pg_tablespace_databases()

A system function listing the OIDs of databases which have objects contained in the specified tablespace

pg_tablespace_databases() is a system function listing the OIDs of databases which have objects contained in the specified tablespace.

pg_tablespace_databases() was added in PostgreSQL 8.0.

Usage

pg_tablespace_databases() must be provided with the OID of the tablespace to query (note there is currently no regtablespace OID type) and returns a list of database OIDs, which can be joined against pg_database to obtain the database name. See below for example.

Currently pg_tablespace_databases() is not used by PostgreSQL itself or any of its client utilities.

pg_tablespace_databases() is useful when preparing to remove a tablespace (using DROP TABLESPACE), as that command can only be executed when the tablespace does not contain any objects from any database.

Change history

Examples

List all databases with objects in tblspace_1:

postgres=# SELECT d.datname
  FROM pg_database d,
  (
    SELECT pg_tablespace_databases(oid) AS datoid
      FROM pg_tablespace t
     WHERE t.spcname='tblspace_1'
  ) t
WHERE t.datoid = d.oid;
 datname  
----------
 testdb
 postgres
(2 rows)

List all tablespaces and show which databases have objects in them:

postgres=# SELECT t.spcname AS "Tablespace",
                  array_to_string(
                    ARRAY(
                        (
                           SELECT datname
                             FROM pg_database
                            WHERE oid IN ( SELECT pg_tablespace_databases(t.oid) AS datoid )
                        ORDER BY 1
                        )
                      ),
                      ','
                  ) AS "Database(s)"
             FROM pg_tablespace t
            WHERE t.spcname != 'pg_global'
         ORDER BY 1;
 Tablespace |             Database(s)             
------------+-------------------------------------
 pg_default | postgres,testdb,template0,template1
 tblspace_1 | postgres,testdb
 tblspace_2 | 
(3 rows)

Note: pg_global is omitted as by definition it cannot contain database data, and including it would result in the following warning being emitted:

WARNING:  global tablespace never has databases

Categories

System function, Tablespaces

See also

tablespace, pg_tablespace_size(), pg_tablespace_location()