`psql` command to view all existing tablespaces?

Question

What's the psql command to view all existing tablespaces?

\l+ displays all existing databases with their configured tablespace, but it won't display tablespaces which have been created but don't yet contain a database.

Sisal

Tablespaces don't "contain databases". They contain tables – Hydrastinine

Ah right, so when you CREATE DATABASE db1 TABLESPACE tp1;, the only purpose this serves is to configure the default tablespace for any table or index created in the database? – Sisal

That's correct, yes. – Hydrastinine

\? to get help for backslash commands. – Gymnasiarch

Answer 1

As documented in the manual, the command to list tablespaces is \db

If you are looking for a command, just enter \? in the psql command line and it will show you all available commands including a short description.

Hydrastinine

Answer 2

PSQL meta-command

\db+

SQL

SELECT * FROM pg_tablespace;

Sarcous

a call for SELECT * FROM pg_tables; (its a "VIEW") might return much more data. – Catanddog

Answer 3

Here is the psql command that you can use:

postgres=# \db+
                                      List of tablespaces
        Name    |  Owner   | Location | Access privileges | Options |  Size  | Description 
    ------------+----------+----------+-------------------+---------+--------+-------------
     pg_default | postgres |          |                   |         | 448 MB | 
     pg_global  | postgres |          |                   |         | 631 kB | 
    (2 rows)

Gwenngwenneth

Answer 4

You can use below query to view all existing tablespaces as well as the location of non default tablespace using psql.

postgres=# select spcname ,pg_tablespace_location(oid) from pg_tablespace;

spcname   |      pg_tablespace_location
-------------+----------------------------------
pg_default  |
pg_global   |
devdefault  | /pgdata/devdata_tbs/devdefault
temp        | /pgdata/devdata_tbs/temporary

Anonym