Thread: [GENERAL] creating tables in tablespace
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)
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)
Tiffany Thang <tiffanythang@gmail.com> writes:
> 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.
An empty entry in pg_tables means the table is in the database's
default tablespace, whether you made it that way implicitly or explicitly.
So this looks as-expected to me. You'd need to spread the database across
more than one tablespace to get anything in that column.
> "show default_tablespace" is also empty.
If you didn't do anything to change that setting, that would also be
expected. Again, the interpretation is "use the database's default
tablespace".
regards, tom lane
Thanks Tom. As the superuser, I'm able to create the table in the specific tablespace. Does myuser require additional privileges?
My aim is to be able to create table in a specific tablespace (in this case, mytablespace) by default without having to explicitly specific one. I would think that is possible but I'm having trouble making it happen. postgres=# create table postgrestab (a int) tablespace mytablespace;
CREATE TABLE
postgres=# select tablename,tableowner,tablespace from pg_tables where tablename like 'post%';
tablename | tableowner | tablespace
-------------+------------+--------------
postgrestab | postgres | mytablespace
(1 row)
Thanks.
On Thu, Aug 24, 2017 at 2:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Tiffany Thang <tiffanythang@gmail.com> writes:
> 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.
An empty entry in pg_tables means the table is in the database's
default tablespace, whether you made it that way implicitly or explicitly.
So this looks as-expected to me. You'd need to spread the database across
more than one tablespace to get anything in that column.
> "show default_tablespace" is also empty.
If you didn't do anything to change that setting, that would also be
expected. Again, the interpretation is "use the database's default
tablespace".
regards, tom lane
Tiffany Thang <tiffanythang@gmail.com> writes:
> Thanks Tom. As the superuser, I'm able to create the table in the specific
> tablespace. Does myuser require additional privileges?
Yes, USAGE on the tablespace if memory serves (check the GRANT man page
for details).
> My aim is to be able to create table in a specific tablespace (in this
> case, mytablespace) by default without having to explicitly specific one. I
> would think that is possible but I'm having trouble making it happen.
Setting default_tablespace, perhaps per-role or per-database if you
don't want to do it within a session, is the way to do that.
regards, tom lane