Thread: null tablespace?

I have not defined my default tablespace:

show default_tablespace ;
 default_tablespace
--------------------


However, I have tables with null tablespaces values in pg_tables:

pg_dw=# select * from pg_tables where tablename = 'source_prev';
 schemaname |       tablename       | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+-----------------------+------------+------------+------------+----------+-------------
 support    | source_prev           | jspool     |            | t          | f        | f


We have defined out search_path correctly, so what tablespace is this table stored in?

Thanks!

Awesome!  We had about 200G worth of tables on our mount point that held
all our WAL data....whoops!  Thanks for the below query.

One last question, can I define a default tablespace per schema?  I
haven't found anything by searching around.

Thanks.
Tony


On Fri, 2011-12-16 at 00:23 +0530, Raghavendra wrote:
> Its going in default tablespace i.e. base/global.
>
>
> Try this query, which give information on the default tablespaces.
>
>
> 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;
>
>
>
>
> ---
> Regards,
> Raghavendra
> EnterpriseDB Corporation
> Blog: http://raghavt.blogspot.com/
>
>
>
> On Fri, Dec 16, 2011 at 12:11 AM, Tony Capobianco
> <tcapobianco@prospectiv.com> wrote:
>         I have not defined my default tablespace:
>
>         show default_tablespace ;
>          default_tablespace
>         --------------------
>
>
>         However, I have tables with null tablespaces values in
>         pg_tables:
>
>         pg_dw=# select * from pg_tables where tablename =
>         'source_prev';
>          schemaname |       tablename       | tableowner | tablespace
>         | hasindexes | hasrules | hastriggers
>         ------------+-----------------------+------------+------------+------------+----------+-------------
>          support    | source_prev           | jspool     |
>          | t          | f        | f
>
>
>         We have defined out search_path correctly, so what tablespace
>         is this table stored in?
>
>         Thanks!
>
>
>         --
>         Sent via pgsql-general mailing list
>         (pgsql-general@postgresql.org)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-general
>
>