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