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.klaver@xxxxxxxxxxx
