On Sat, Oct 25, 2025 at 1:48 AM Nathan Bossart <[email protected]> wrote: > > On Sun, Sep 28, 2025 at 08:28:45PM -0500, David G. Johnston wrote: > > On Sunday, September 28, 2025, jian he <[email protected]> wrote: > >> Since we already support INCLUDING STORAGE, I’m not sure that > >> INCLUDING STORAGE PARAMETER is the right syntax to go with. > > > > I’d just call it “including parameters”; sure, all of the existing ones are > > storage related, but if there were non-storage ones we’d include those as > > well, so qualifying with a parameter type doesn’t seem correct. > > +1 > > -- > nathan
hi. attached patch using syntax: CREATE TABLE LIKE INCLUDING PARAMETERS.
From 16dd551ad89c999d5e4ec654e20df7ac62df6a2d Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Thu, 30 Oct 2025 09:53:22 +0800 Subject: [PATCH v4 1/1] create table like incluing parameters demo: create table t(a text) with (fillfactor = 100, toast.vacuum_truncate=true); create table t2(like t including parameters); \d+ t2 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/CACJufxHr=nKEsS66M7rTHgB+mXdQ948=_ej1jztac7pt-ej...@mail.gmail.com --- doc/src/sgml/ref/create_table.sgml | 12 +++- src/backend/access/common/reloptions.c | 50 +++++++++++++++ src/backend/parser/gram.y | 5 +- src/backend/parser/parse_utilcmd.c | 64 +++++++++++++++++++ src/include/access/reloptions.h | 1 + src/include/nodes/parsenodes.h | 1 + src/include/parser/kwlist.h | 1 + .../regress/expected/create_table_like.out | 39 +++++++++++ src/test/regress/sql/create_table_like.sql | 28 ++++++++ 9 files changed, 199 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index a157a244e4e..cad839b452d 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 | PARAMETERS | ALL } <phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase> @@ -753,6 +753,16 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </listitem> </varlistentry> + <varlistentry id="sql-createtable-parms-like-opt-storage-paramter"> + <term><literal>INCLUDING PARAMETERS</literal></term> + <listitem> + <para> + All parameters, such as the <literal>STORAGE PARAMETER</literal> settings of the source table, will be copied. + For table storage parameters, see <xref linkend="sql-createtable-storage-parameters"/> below for more information. + </para> + </listitem> + </varlistentry> + <varlistentry id="sql-createtable-parms-like-opt-statistics"> <term><literal>INCLUDING STATISTICS</literal></term> <listitem> diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index 9e288dfecbf..365f30402f0 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -1392,6 +1392,56 @@ 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, otherwise 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 (DatumGetPointer(options) == NULL) + 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 a4b29c822e8..fe4b035fe34 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -762,7 +762,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); ORDER ORDINALITY OTHERS OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER - PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD PATH + PARALLEL PARAMETER PARAMETERS PARSER PARTIAL PARTITION PASSING PASSWORD PATH PERIOD PLACING PLAN PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION @@ -4223,6 +4223,7 @@ TableLikeOption: | INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; } | STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; } | STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; } + | PARAMETERS { $$ = CREATE_TABLE_LIKE_PARAMETERS; } | ALL { $$ = CREATE_TABLE_LIKE_ALL; } ; @@ -17994,6 +17995,7 @@ unreserved_keyword: | OWNER | PARALLEL | PARAMETER + | PARAMETERS | PARSER | PARTIAL | PARTITION @@ -18622,6 +18624,7 @@ bare_label_keyword: | OWNER | PARALLEL | PARAMETER + | PARAMETERS | PARSER | PARTIAL | PARTITION diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index e96b38a59d5..791ffc5150c 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,66 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla } } + /* Likewise, copy paramters if requested */ + if ((table_like_clause->options & CREATE_TABLE_LIKE_PARAMETERS) && + !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); + + /* get the 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 +3638,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 ecbddd12e1b..a8e5538119c 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -795,6 +795,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_PARAMETERS = 1 << 9, CREATE_TABLE_LIKE_ALL = PG_INT32_MAX } TableLikeOption; diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 84182eaaae2..d132eca0068 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -335,6 +335,7 @@ PG_KEYWORD("owned", OWNED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("owner", OWNER, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("parallel", PARALLEL, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("parameter", PARAMETER, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("parameters", PARAMETERS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("parser", PARSER, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index d3c35c14847..2e8962590a0 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 PARAMETERS +create table t_storage(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_storage including parameters) with (fillfactor = 100); --error +ERROR: parameter "fillfactor" specified more than once +create table t_storage1(like t_storage excluding parameters) with (fillfactor = 100); --ok +\d+ t_storage1 + Table "public.t_storage1" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+------+-----------+----------+---------+----------+--------------+------------- + a | text | | | | extended | | +Options: fillfactor=100 + +create table t_storage2(like t_storage including parameters) 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_storage', 't_storage2') +order by c.relname \gx +-[ RECORD 1 ]-+----------------------------------------------------------------------------------------------------------------------------------------------------- +relname | t_storage +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 | t_storage2 +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_storage, t_storage1, t_storage2; diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index 93389b57dbf..e1abf5a4262 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 PARAMETERS +create table t_storage(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_storage including parameters) with (fillfactor = 100); --error +create table t_storage1(like t_storage excluding parameters) with (fillfactor = 100); --ok +\d+ t_storage1 + +create table t_storage2(like t_storage including parameters) 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_storage', 't_storage2') +order by c.relname \gx + +drop table t_storage, t_storage1, t_storage2; -- 2.34.1
