how to find a tablespace for the table?

hello,

I'm looking into pg_tables view and only one tablespace is displayed is
pg_global.
All my tables are created in my custom tablespace and that column is empty
for them.

select * from pg_tablespace show my tablespace, pgAdmin shows that
tablespace for each table. I need to query the data dictionary to find the
tablespace for the table in my proc.

select t.spcname, c.relname AS tablename from pg_class c , pg_tablespace
t
where t.oid = c.reltablespace

does not show my tables, only the dd tables.

SELECT COALESCE(tbs.spcname, '*') AS tbsname
FROM pg_catalog.pg_class AS t
JOIN pg_catalog.pg_namespace AS s
ON (s.oid = t.relnamespace)
LEFT OUTER JOIN pg_catalog.pg_tablespace AS tbs
ON (tbs.oid = t.reltablespace)
WHERE t.relname like 'summ%' AND s.nspname = 'public';

returns *

please help.

thank you.

Helen

--
View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4500200.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.