How to set tablespace for a specific CREATE TABLE [Table] AS

Question

How to set tablespace for a specific table creation AS clause?, like:

CREATE TABLE [New Table] AS 
TABLE [Old Table] 
--WITH NO DATA
;

In which tablespace new table with/without data is created for this specific 'AS' clause?

In official documentation, have not found such case: https://www.postgresql.org/docs/current/sql-createtable.html , though it says:

Tablespaces

The PostgreSQL concept of tablespaces is not part of the standard. Hence, the clauses TABLESPACE and USING INDEX TABLESPACE are extensions.

So, please help me out, what is default tablespace will be used in such "AS" creation case of the table, and how to specify custom table space for this. By ALTER -ing created table?, like only:

ALTER TABLE [New Table] 
SET TABLESPACE [Existing Tablespace];

"*what is default tablespace will be used*" - the same as for all other tables that don't specify a tablespace: the default tablespace which is the directory that was created uring `initdb`. Your second statement is the correct way to change the tablespace of an existing table. Note that tablespaces are rarely used in Postgres. Are you sure you need them? — , Jan 16 '22 at 11:57
@a_horse_with_no_name but what if data will be moved from originated table with huge-disk-space-tablespace to new table with default tablespace, that has limited disk-space-tablespace? Is it ok? — Gen Eva, Jan 16 '22 at 12:00

score 1 · Accepted Answer · edited Jan 16 '22 at 14:01

1

As documented in the manual you can append the tablespace option to a CREATE TABLE AS statement:

CREATE TABLE new_table 
TABLESPACE custom_tablespace
AS 
TABLE old_table
WITH NO DATA;

edited Jan 16 '22 at 14:01

Gen Eva

39
6

answered Jan 16 '22 at 11:59

How to set tablespace for a specific CREATE TABLE [Table] AS

1 Answers1