Re: how to find a tablespace for the table?

On 2/22/20 10:34 AM, Daulat Ram wrote:
> Hi team,
>
> how to find a tablespace for the table?
>
> See my comments below:
>
> I have created a database with default tablespace like below:
>
> edb=# CREATE DATABASE conndb WITH TABLESPACE = conn_s_tables;
>
> After that I have created a table
>
> CREATE TABLE COMPANY_new(
>
>    ID INT PRIMARY KEY     NOT NULL,
>
>    NAME           TEXT    NOT NULL,
>
>    AGE            INT     NOT NULL,
>
>    ADDRESS        CHAR(50),
>
>    SALARY         REAL,
>
>    JOIN_DATE        DATE
>
> ) ,
>
> CREATE TABLE COMPANY_new(
>
>    ID INT PRIMARY KEY     NOT NULL,
>
>    NAME           TEXT    NOT NULL,
>
>    AGE            INT     NOT NULL,
>
>    ADDRESS        CHAR(50),
>
>    SALARY         REAL,
>
>    JOIN_DATE        DATE
>
> )
>
> tablespace conn_s_tables ;
>
> But I am unable to search the tablespace name where tablespace exist ,
> tablespace column is blank.

https://www.postgresql.org/docs/12/view-pg-tables.html

tablespace name pg_tablespace.spcname Name of tablespace containing
table (null if default for database)

https://www.postgresql.org/docs/12/sql-createdatabase.html

tablespace_name

The name of the tablespace that will be associated with the new
database, or DEFAULT to use the template database's tablespace. This
tablespace will be the default tablespace used for objects created in
this database. See CREATE TABLESPACE for more information.

So conn_s_tables is default for conndb, therefore it will not show up in
queries below.

If you want to find the default tablespace:

https://www.postgresql.org/docs/12/catalog-pg-database.html

dattablespace oid pg_tablespace.oid The default tablespace for the
database. Within this database, all tables for which
pg_class.reltablespace is zero will be stored in this tablespace; in
particular, all the non-shared system catalogs will be there.

>
> conndb=# select schemaname,tablename,tableowner,tablespace from
> pg_tables where tablename='company';
>
> schemaname | tablename |  tableowner  | tablespace
>
> ------------+-----------+--------------+------------
>
> conndb     | company   | enterprisedb |
>
> (1 row)
>
> conndb=# select schemaname,tablename,tableowner,tablespace from
> pg_tables where tablename='company_new';
>
> schemaname |  tablename  |  tableowner  | tablespace
>
> ------------+-------------+--------------+------------
>
> conndb     | company_new | enterprisedb |
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com