Обсуждение: Tablespace Default Behavior

I am somewhat new to Postgresql.  I cant seem to create a table in a
non-default tablespace.  In the postgresql.conf file,  I have:
default_tablespace = ''".

When I create a table, like this:

    CREATE TABLE myschema.mytable (rc_id integer NOT NULL) TABLESPACE
my_space;

and then I do:

    select * from pg_tables where schemaname ='myschema';

the tablespace is blank for the new table.  When I look in PgAdmin, the
tablespace shows up as "pg_default."   What do I need to change to create
the table in the desired tablespace?  Thank you.




--
View this message in context: http://www.postgresql-archive.org/Tablespace-Default-Behavior-tp5952910.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

More Info Edit:   Also, the database is in the my_space tablespace.  The
location for the my_space tablespace is a different folder than the $PGDATA
folder.



--
View this message in context: http://www.postgresql-archive.org/Tablespace-Default-Behavior-tp5952910p5952917.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

harpagornis <shenlong@runbox.com> writes:
> When I create a table, like this:
>     CREATE TABLE myschema.mytable (rc_id integer NOT NULL) TABLESPACE my_space;
> and then I do:
>     select * from pg_tables where schemaname ='myschema';
> the tablespace is blank for the new table.  When I look in PgAdmin, the
> tablespace shows up as "pg_default."   What do I need to change to create
> the table in the desired tablespace?

As you mention in your followup, the database's default tablespace is
'my_space', so the table is actually in the correct tablespace.

For implementation reasons, we force tables that are placed in the
database's default tablespace to be recorded as "stored in database's
tablespace", which shows up as blank in pg_tables.  That means that if you
change the database's tablespace the table will move along with the
rest of the database.  You might consider that either a feature or a
bug depending on your use-case, but that's how it works ...

            regards, tom lane

That is what I suspected, and thank you for the explanation.  I think it is
misleading and a bug in PgAdmin for explicitly listing the tablespace as
pg_default.



--
View this message in context: http://www.postgresql-archive.org/Tablespace-Default-Behavior-tp5952910p5952929.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.