hi.
attached patch is to make CREATE TABLE LIKE copy source relation
storage parameter
demo:
create table t(a text) with (fillfactor = 100, toast.vacuum_truncate=true);
create table t2(like t including storage parameter) with (parallel_workers = 3);
Table "public.t2"
Column | Type | Collation | Nullable | Default | Storage |
Compression | Stats target | Description
--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
a | text | | | | extended |
| |
Access method: heap
Options: parallel_workers=3, fillfactor=100, toast.vacuum_truncate=true
of course, duplicate storage mention would result error,
for example:
create table t3(like t including storage parameter) with (fillfactor = 100);
Since we already support INCLUDING STORAGE, I’m not sure that
INCLUDING STORAGE PARAMETER is the right syntax to go with.
From a497b0309772b7c202d54b65f9c96b655ca40179 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Sun, 28 Sep 2025 19:20:00 +0800
Subject: [PATCH v1 1/1] create table like incluing storage parameter
demo:
create table t(a text) with (fillfactor = 100, toast.vacuum_truncate=true);
create table t2(like t including storage parameter);
Table "public.t2"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
a | text | | | | extended | | |
Access method: heap
Options: fillfactor=100, toast.vacuum_truncate=true
discussion: https://postgr.es/m/
---
doc/src/sgml/ref/create_table.sgml | 19 +++++-
src/backend/access/common/reloptions.c | 49 +++++++++++++++
src/backend/parser/gram.y | 1 +
src/backend/parser/parse_utilcmd.c | 63 +++++++++++++++++++
src/include/access/reloptions.h | 1 +
src/include/nodes/parsenodes.h | 1 +
.../regress/expected/create_table_like.out | 39 ++++++++++++
src/test/regress/sql/create_table_like.sql | 28 +++++++++
8 files changed, 200 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index dc000e913c1..a389ce597a9 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -88,7 +88,7 @@ class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable cl
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | STORAGE PARAMETER | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -776,6 +776,23 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-createtable-parms-like-opt-storage-paramter">
+ <term><literal>INCLUDING STORAGE PARAMETER</literal></term>
+ <listitem>
+ <para>
+ <literal>STORAGE PARAMETER</literal> settings for the copied table will be copied.
+ For table storage parameters, see <xref linkend="sql-createtable-storage-parameters"/> below for more information.
+ </para>
+
+ <para>
+ Do not confuse this with <literal>INCLUDING STORAGE</literal>.
+ <literal>INCLUDING STORAGE</literal> is for copying table column storage property,
+ this is for copying table storage parameters.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createtable-parms-like-opt-all">
<term><literal>INCLUDING ALL</literal></term>
<listitem>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 0af3fea68fa..745a71d5192 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -1383,6 +1383,55 @@ untransformRelOptions(Datum options)
return result;
}
+/*
+ * Convert the reloptions from text-array format into a List of DefElem. This
+ * is the reverse operation of transformRelOptions(). If any option includes a
+ * namespace qualification, create the DefElem in that namespace.
+ * If nameSpace is NULL, this behave the same as untransformRelOptions.
+ */
+List *
+untransformRelOptionsExtended(Datum options, char* nameSpace)
+{
+ List *result = NIL;
+ ArrayType *array;
+ Datum *optiondatums;
+ int noptions;
+ int i;
+
+ /* Nothing to do if no options */
+ if (!PointerIsValid(DatumGetPointer(options)))
+ return result;
+
+ array = DatumGetArrayTypeP(options);
+
+ deconstruct_array_builtin(array, TEXTOID, &optiondatums, NULL, &noptions);
+
+ for (i = 0; i < noptions; i++)
+ {
+ char *s;
+ char *p;
+ Node *val = NULL;
+
+ s = TextDatumGetCString(optiondatums[i]);
+ p = strchr(s, '=');
+ if (p)
+ {
+ *p++ = '\0';
+ val = (Node *) makeString(p);
+ }
+
+ if (nameSpace == NULL)
+ result = lappend(result, makeDefElem(s, val, -1));
+ else
+ result = lappend(result, makeDefElemExtended(nameSpace, s, val,
+ DEFELEM_UNSPEC,
+ -1));
+ }
+
+ return result;
+}
+
+
/*
* Extract and parse reloptions from a pg_class tuple.
*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9fd48acb1f8..baa9108ac43 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4216,6 +4216,7 @@ TableLikeOption:
| INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; }
| STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; }
| STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; }
+ | STORAGE PARAMETER { $$ = CREATE_TABLE_LIKE_STORAGE_PARAMETER; }
| ALL { $$ = CREATE_TABLE_LIKE_ALL; }
;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index e96b38a59d5..bd401cba5e4 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -85,6 +85,7 @@ typedef struct
List *fkconstraints; /* FOREIGN KEY constraints */
List *ixconstraints; /* index-creating constraints */
List *likeclauses; /* LIKE clauses that need post-processing */
+ List *options; /* options from WITH clause, table AM specific parameters */
List *blist; /* "before list" of things to do before
* creating the table */
List *alist; /* "after list" of things to do after creating
@@ -245,6 +246,7 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
cxt.fkconstraints = NIL;
cxt.ixconstraints = NIL;
cxt.likeclauses = NIL;
+ cxt.options = stmt->options;
cxt.blist = NIL;
cxt.alist = NIL;
cxt.pkey = NULL;
@@ -369,6 +371,7 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
stmt->tableElts = cxt.columns;
stmt->constraints = cxt.ckconstraints;
stmt->nnconstraints = cxt.nnconstraints;
+ stmt->options = cxt.options;
result = lappend(cxt.blist, stmt);
result = list_concat(result, cxt.alist);
@@ -1268,6 +1271,65 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
}
}
+ /* Likewise, copy storage paramter if requested */
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_STORAGE_PARAMETER) &&
+ !cxt->isforeign)
+ {
+ HeapTuple tuple;
+ Datum reloptions;
+ bool isnull;
+ Oid relid;
+ List *oldoptions = NIL;
+ List *oldtoastoptions = NIL;
+
+ relid = RelationGetRelid(relation);
+ tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u", relid);
+
+ reloptions = SysCacheGetAttr(RELOID, tuple,
+ Anum_pg_class_reloptions, &isnull);
+
+ if (!isnull)
+ {
+ oldoptions = untransformRelOptions(reloptions);
+
+ foreach_node(DefElem, option, oldoptions)
+ cxt->options = lappend(cxt->options, option);
+ }
+
+ ReleaseSysCache(tuple);
+
+ /* now get the source relation toast relation reloptions */
+ if (OidIsValid(relation->rd_rel->reltoastrelid))
+ {
+ Relation toastrel;
+
+ Oid toastid = relation->rd_rel->reltoastrelid;
+
+ toastrel = table_open(toastid, AccessShareLock);
+
+ /* Fetch heap tuple */
+ tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(toastid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for toast relation %u", toastid);
+
+ /* Get the toast reloptions */
+ reloptions = SysCacheGetAttr(RELOID, tuple, Anum_pg_class_reloptions,
+ &isnull);
+ if (!isnull)
+ {
+ oldtoastoptions = untransformRelOptionsExtended(reloptions, "toast");
+ foreach_node(DefElem, option, oldtoastoptions)
+ cxt->options = lappend(cxt->options, option);
+ }
+
+ ReleaseSysCache(tuple);
+
+ table_close(toastrel, NoLock);
+ }
+ }
+
/*
* Reproduce not-null constraints, if any, by copying them. We do this
* regardless of options given.
@@ -3575,6 +3637,7 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
cxt.fkconstraints = NIL;
cxt.ixconstraints = NIL;
cxt.likeclauses = NIL;
+ cxt.options = NIL;
cxt.blist = NIL;
cxt.alist = NIL;
cxt.pkey = NULL;
diff --git a/src/include/access/reloptions.h b/src/include/access/reloptions.h
index a604a4702c3..8589616d08b 100644
--- a/src/include/access/reloptions.h
+++ b/src/include/access/reloptions.h
@@ -236,6 +236,7 @@ extern Datum transformRelOptions(Datum oldOptions, List *defList,
const char *nameSpace, const char *const validnsps[],
bool acceptOidsOff, bool isReset);
extern List *untransformRelOptions(Datum options);
+extern List *untransformRelOptionsExtended(Datum options, char* nameSpace);
extern bytea *extractRelOptions(HeapTuple tuple, TupleDesc tupdesc,
amoptions_function amoptions);
extern void *build_reloptions(Datum reloptions, bool validate,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 86a236bd58b..97dbb70dfc0 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -793,6 +793,7 @@ typedef enum TableLikeOption
CREATE_TABLE_LIKE_INDEXES = 1 << 6,
CREATE_TABLE_LIKE_STATISTICS = 1 << 7,
CREATE_TABLE_LIKE_STORAGE = 1 << 8,
+ CREATE_TABLE_LIKE_STORAGE_PARAMETER = 1 << 9,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index d3c35c14847..193771f3408 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -699,3 +699,42 @@ DROP FOREIGN TABLE ctl_foreign_table1;
DROP FOREIGN TABLE ctl_foreign_table2;
DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
NOTICE: drop cascades to server ctl_s0
+--CREATE TABLE LIKE with STORAGE PARAMETER
+create table t(a text) with (
+ fillfactor = 100,
+ toast_tuple_target = 128,
+ vacuum_index_cleanup = auto,
+ toast.vacuum_index_cleanup = auto,
+ vacuum_truncate = true,
+ toast.vacuum_truncate = false,
+ log_autovacuum_min_duration = 100,
+ toast.log_autovacuum_min_duration = 100);
+create table t1(like t including storage parameter) with (fillfactor = 100); --error
+ERROR: parameter "fillfactor" specified more than once
+create table t1(like t excluding storage parameter) with (fillfactor = 100); --ok
+\d+ t1
+ Table "public.t1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+Options: fillfactor=100
+
+create table t2(like t including storage parameter) with (
+ parallel_workers = 3,
+ toast.autovacuum_vacuum_threshold = 101,
+ toast.autovacuum_vacuum_scale_factor = 0.3);
+select c.relname, c.reloptions, tc.reloptions as toast_options
+from pg_catalog.pg_class c
+left join pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
+where c.relname in ('t', 't2')
+order by c.relname \gx
+-[ RECORD 1 ]-+-----------------------------------------------------------------------------------------------------------------------------------------------------
+relname | t
+reloptions | {fillfactor=100,toast_tuple_target=128,vacuum_index_cleanup=auto,vacuum_truncate=true,log_autovacuum_min_duration=100}
+toast_options | {vacuum_index_cleanup=auto,vacuum_truncate=false,log_autovacuum_min_duration=100}
+-[ RECORD 2 ]-+-----------------------------------------------------------------------------------------------------------------------------------------------------
+relname | t2
+reloptions | {parallel_workers=3,fillfactor=100,toast_tuple_target=128,vacuum_index_cleanup=auto,vacuum_truncate=true,log_autovacuum_min_duration=100}
+toast_options | {autovacuum_vacuum_threshold=101,autovacuum_vacuum_scale_factor=0.3,vacuum_index_cleanup=auto,vacuum_truncate=false,log_autovacuum_min_duration=100}
+
+drop table t, t1, t2;
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 93389b57dbf..47f67d244e2 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -280,3 +280,31 @@ DROP TABLE ctl_table;
DROP FOREIGN TABLE ctl_foreign_table1;
DROP FOREIGN TABLE ctl_foreign_table2;
DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
+
+--CREATE TABLE LIKE with STORAGE PARAMETER
+create table t(a text) with (
+ fillfactor = 100,
+ toast_tuple_target = 128,
+ vacuum_index_cleanup = auto,
+ toast.vacuum_index_cleanup = auto,
+ vacuum_truncate = true,
+ toast.vacuum_truncate = false,
+ log_autovacuum_min_duration = 100,
+ toast.log_autovacuum_min_duration = 100);
+
+create table t1(like t including storage parameter) with (fillfactor = 100); --error
+create table t1(like t excluding storage parameter) with (fillfactor = 100); --ok
+\d+ t1
+
+create table t2(like t including storage parameter) with (
+ parallel_workers = 3,
+ toast.autovacuum_vacuum_threshold = 101,
+ toast.autovacuum_vacuum_scale_factor = 0.3);
+
+select c.relname, c.reloptions, tc.reloptions as toast_options
+from pg_catalog.pg_class c
+left join pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
+where c.relname in ('t', 't2')
+order by c.relname \gx
+
+drop table t, t1, t2;
--
2.34.1
