Re: how to find a tablespace for the table?

On 06/17/2011 06:50 PM, hyelluas wrote:
> 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.
>

I'm not sure what's wrong here, but the query you are trying to use to
decode this information doesn't look quite right. pg_tables is just a
regular query; here is its source code:

CREATE VIEW pg_tables AS
SELECT
N.nspname AS schemaname,
C.relname AS tablename,
pg_get_userbyid(C.relowner) AS tableowner,
T.spcname AS tablespace,
C.relhasindex AS hasindexes,
C.relhasrules AS hasrules,
C.relhastriggers AS hastriggers
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)
WHERE C.relkind = 'r';

I think that if you start with this and try to experiment from there,
you may be able to figure out what's going on here a little better.
This connects up the main relevant tables in the right way.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books