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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books