CODEWITHSUNDEEP
postgresqlpostgresql-9.6
ramsey_lewis
ramsey_lewis

Reputation: 598

postgres 9.6 show default_tablespace

I have a tablespace on my bdd tbs1 but I want to know what is the default tablespace. My problem is that the command to show the default table space doesn't return anything :

postgres=# show default_tablespace ;
 default_tablespace
--------------------

(1 row)

postgres=#

And when I try to show all the tablespaces :

postgres=# \db+
                                                  List of tablespaces
    Name    |  Owner   |                Location                 | Access privileges | Options |   Size   | Description
------------+----------+-----------------------------------------+-------------------+---------+----------+-------------
pg_default | postgres |                                         |                   |         | 326 MB   |
pg_global  | postgres |                                         |                   |         | 1241 kB  |
tbs1    | fdu      | ... |                   |         | 553 MB |
(3 rows)

If the show default_tablespace command doens't return anything, does this means the default tablespace is pg_default ?

thanks

Upvotes: 2

Views: 7706

Answers (3)

cavagli
cavagli

Reputation: 1

If you want to set a specific tablespace for your user you just have to execute this (as postgres or other superuser)

ALTER USER myuser SET default_tablespace TO mytablespace;

Upvotes: 0

Peter
Peter

Reputation: 1260

I had a hard time finding the default tablespace in my PostgreSQL 13 database. The show default_tablespace statement didn't return anything like the OP said. The pg_class and pg_tables weren't very useful either.

But I did stumble across a command line tool called oid2name that returned what I needed.

There source code for oid2name is here. I modified one of the queries to this and it seems to work:

SELECT spcname
FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t 
ON (dattablespace = t.oid) 
where datname = (SELECT current_database())
ORDER BY 2

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 246578

Yes, by default pg_default is the default tablespace.

You can change the default with the default_tablespace parameter or by changing the tablespace of the database you are connected to.

Mind that in 99% of all PostgreSQL installations you don't need to define tablespaces.

Upvotes: 3

Related Questions