Re: how to find a tablespace for the table?

Поиск
Список
Период
Сортировка
От Raghavendra
Тема Re: how to find a tablespace for the table?
Дата
Msg-id BANLkTinhDx4WfihvQmFNVn5KZs49NwJxMw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: how to find a tablespace for the table?  (hyelluas <helen_yelluas@mcafee.com>)
Ответы Re: how to find a tablespace for the table?  (hyelluas <helen_yelluas@mcafee.com>)
Список pgsql-general
Дерево обсуждения
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



On Mon, Jun 20, 2011 at 11:40 PM, hyelluas <helen_yelluas@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

В списке pgsql-general по дате отправления:

Предыдущее
От: Tarlika Elisabeth Schmitz
Дата:
Сообщение: Re: unique across two tables
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Forward referencing of table aliases in subqueries does not work in 9.1 beta2 ( works in 9.0 and 8.4.2 )