creating tables in tablespace

[Tiffany Thang <tiffanythang@xxxxxxxxx>]

Hi,
I'm not able to create tables in a specific tablespace. I'm not sure what I have missed.

According to the documentation, a table can be created in a specific tablespace by performing the following:
1. Specify the tablespace parameter in the create database statement.  
2. Specify the tablespace parameter in the create table statement.


I've tried both but the tablespace column in pg_tables is empty. "show default_tablespace" is also empty. Can someone help?

Thanks.


As the superuser,
create tablespace mytablespace owner myuser location '/pgsqldata/mydb';
create database mydb owner=myuser tablespace=mytablespace;

As myuser,
mydb=> select current_user;
 current_user
--------------
 myuser
(1 row)

mydb=> create table tab1 (a int);
CREATE TABLE
mydb=> create table tab2 (a int) tablespace mytablespace;
CREATE TABLE
mydb=> show default_tablespace;
 default_tablespace
--------------------

(1 row)


mydb=> select tablename,tableowner,tablespace from pg_tables where tablename like 'tab%';
 tablename | tableowner | tablespace
-----------+------------+------------
 tab1      | myuser     |
 tab2      | myuser     |
(2 rows)