Contents
pg_tablespace_databases()
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
- PostgreSQL 8.0
- added (commit 0b89d261)
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
References
- PostgreSQL documentation: System Catalog Information Functions