That's right, if the tables are in default tablespace, those columns will be
blank, if any of the table created under any of the
tablespace then it will show up.
Eg:-
postgres=# show default_tablespace ; (this would be blank becz am in
pg_default/pg_global)
default_tablespace
--------------------
(1 row)
postgres=# create table foo(id int);
CREATE TABLE
postgres=# select * from pg_tables where tablename='foo';
-[ RECORD 1 ]---------
schemaname | public
tablename | foo
tableowner | postgres
tablespace |
hasindexes | f
hasrules | f
hastriggers | f
Now I have the table in one of my tablespace.
postgres=#create table tab_test(id int) tablespace t1;
Expanded display is on.
postgres=# select * from pg_tables where tablename='tab_test';
-[ RECORD 1 ]---------
schemaname | public
tablename | tab_test
tableowner | postgres
*tablespace | t1*
hasindexes | f
hasrules | f
hastriggers | f
If you want to know the tablespace default information, you can try with
this query.
select spcname, case spcname when 'pg_default' then (select setting from
pg_settings where name = 'data_directory')||'/base' when 'pg_global' then
(select setting from pg_settings where name = 'data_directory')||'/global'
else spclocation end from pg_tablespace;
To get the exact table's and its tablespace's below query will work.
select relname,reltablespace from pg_class where reltablespace in(select
oid from pg_tablespace where spcname not in ('pg_default','pg_global'));
---
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
On Mon, Jun 20, 2011 at 11:40 PM, hyelluas <helen_yell...@mcafee.com> wrote:
> thank you Greg,
>
> here is what I get, I createed view as you suggested.
> I'm not sure why tablespace column is empty
>
> profiler1=# select * from pg_tables where schemaname ='public' limit 10;
> schemaname | tablename | tableowner | tablespace |
> hasindexes
> | hasrules | hastri
> ers
>
> ------------+-------------------------+------------+------------+------------+----------+-------
> ----
> public | ttt | postgres |
> | f | f | f
> public | summ_hrly_1514609 | postgres | | t
> | f | f
> public | summ_5min_1514610 | postgres | | t
> | f | f
> public | exp_cnt | postgres |
> | f | f | f
> public | auth_type | postgres |
> |
> t | f | f
> public | druid_mapping | postgres | |
> t | f | f
> public | application_category | postgres | | t
> | f | f
> public | application_risk | postgres |
> | t | f | f
> public | policy_history | postgres |
> | t | f | f
> public | datasource | postgres |
> |
> t | f | f
> (10 rows)
>
>
> thank you.
> Helen
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4507266.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
