Re: how to find a tablespace for the table?

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_yelluas(at)mcafee(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>