Thread: how to find a tablespace for the table?
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.
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 |
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@aklaver.com