On 06/02/26 12:46, I wrote:
More generally maybe start with the documentation to define exactly
how it
should behave and what limitations it would have (i.e., it isn’t
going to
re-point schema references in black-box function bodies).
Good point, I'll also include some initial documentation changes on
the next version.
So here is V2 with some documentation changes and also with the index
name not being preserved issue that Marcos have mentioned earlier fixed.
I'm still working on the function support.
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
From 1436d7a2758ef8913e09f34cdee8f2f13c0ad01b Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Tue, 3 Feb 2026 17:57:47 -0300
Subject: [PATCH v2 1/2] Add CREATE SCHEMA ... LIKE support
This patch introduces a new LIKE clause for CREATE SCHEMA that allows
copying table definitions from an existing schema to a new one.
Syntax:
CREATE SCHEMA <name> LIKE <source_schema> [like_options...]
Where like_options is:
{ INCLUDING | EXCLUDING } { TABLE | INDEX | ALL }
The implementation reuses the existing CREATE TABLE ... LIKE
infrastructure to copy table structures, including columns, defaults,
constraints, and optionally indexes.
---
doc/src/sgml/ref/create_schema.sgml | 70 ++++++++-
src/backend/commands/indexcmds.c | 3 +-
src/backend/commands/schemacmds.c | 157 ++++++++++++++++++++
src/backend/commands/tablecmds.c | 4 +-
src/backend/parser/gram.y | 53 +++++++
src/backend/parser/parse_utilcmd.c | 17 ++-
src/include/nodes/parsenodes.h | 17 +++
src/include/parser/parse_utilcmd.h | 3 +-
src/test/regress/expected/create_schema.out | 109 ++++++++++++++
src/test/regress/sql/create_schema.sql | 68 +++++++++
src/tools/pgindent/typedefs.list | 1 +
11 files changed, 492 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/ref/create_schema.sgml
b/doc/src/sgml/ref/create_schema.sgml
index ed69298ccc6..a96a43f9a31 100644
--- a/doc/src/sgml/ref/create_schema.sgml
+++ b/doc/src/sgml/ref/create_schema.sgml
@@ -22,6 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
CREATE SCHEMA <replaceable class="parameter">schema_name</replaceable> [
AUTHORIZATION <replaceable class="parameter">role_specification</replaceable> ]
[ <replaceable class="parameter">schema_element</replaceable> [ ... ] ]
+CREATE SCHEMA <replaceable class="parameter">schema_name</replaceable> [ IF
NOT EXISTS ] LIKE <replaceable class="parameter">source_schema</replaceable> [
<replaceable class="parameter">like_option</replaceable> [ ... ] ]
CREATE SCHEMA AUTHORIZATION <replaceable
class="parameter">role_specification</replaceable> [ <replaceable
class="parameter">schema_element</replaceable> [ ... ] ]
CREATE SCHEMA IF NOT EXISTS <replaceable
class="parameter">schema_name</replaceable> [ AUTHORIZATION <replaceable
class="parameter">role_specification</replaceable> ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable
class="parameter">role_specification</replaceable>
@@ -32,6 +33,10 @@ CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable
class="parameter">role_sp
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER
+
+<phrase>and <replaceable class="parameter">like_option</replaceable> can
be:</phrase>
+
+ { INCLUDING | EXCLUDING } { TABLE | INDEX | ALL }
</synopsis>
</refsynopsisdiv>
@@ -120,6 +125,65 @@ CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable
class="parameter">role_sp
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>LIKE <replaceable>source_schema</replaceable> [
<replaceable>like_option</replaceable> ... ]</literal></term>
+ <listitem>
+ <para>
+ The <literal>LIKE</literal> clause specifies a schema from which the
new
+ schema automatically copies the definitions of all supported objects
+ (such as tables and indexes).
+ </para>
+
+ <para>
+ The new schema and the original schema are completely decoupled after
+ creation is complete. Changes to objects in the original schema will
+ not be applied to the new schema, and changes to the new schema will
+ not affect the original.
+ </para>
+
+ <para>
+ The optional <replaceable>like_option</replaceable> clauses specify
+ which additional properties of the original schema's objects to copy.
+ Specifying <literal>INCLUDING</literal> copies the object, while
+ specifying <literal>EXCLUDING</literal> omits the object.
+ <literal>EXCLUDING</literal> is the default. If multiple
+ specifications are made for the same kind of object, the last one
+ is used. The available options are:
+ </para>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>INCLUDING TABLE</literal></term>
+ <listitem>
+ <para>
+ Definitions of tables and their associated sequences for identity
+ columns are copied to the new schema. Note that for
+ <type>SERIAL</type> data types, the sequence is shared with the
+ source schema; a new sequence is not created for the new table,
+ meaning the new table's column will default to the original
sequence.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INCLUDING INDEX</literal></term>
+ <listitem>
+ <para>
+ Indexes from the source schema will be created on the new tables.
+ However, this option only takes effect if <literal>INCLUDING
TABLE</literal>
+ is also active. If tables are excluded (either by default or
explicitly),
+ indexes will not be created, as they have no parent object to
attach to.
+ For example, <literal>CREATE SCHEMA foo LIKE bar INCLUDING
INDEX</literal>
+ or <literal>CREATE SCHEMA foo LIKE bar INCLUDING ALL EXCLUDING
TABLE</literal>
+ will result in no indexes being created.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </listitem>
+ </varlistentry>
</variablelist>
</refsect1>
@@ -128,8 +192,10 @@ CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable
class="parameter">role_sp
<para>
To create a schema, the invoking user must have the
- <literal>CREATE</literal> privilege for the current database.
- (Of course, superusers bypass this check.)
+ <literal>CREATE</literal> privilege for the current database. For
+ <literal>LIKE</literal> the user also must have the <literal>USAGE</literal>
+ privilege on the source schema.
+ (Of course, superusers bypass these checks.)
</para>
</refsect1>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 635679cc1f2..eeac9919fdd 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1515,7 +1515,8 @@ DefineIndex(ParseState *pstate,
childStmt =
generateClonedIndexStmt(NULL,
parentIndex,
attmap,
-
NULL);
+
NULL,
+
false);
/*
* Recurse as the starting user ID.
Callee will use that
diff --git a/src/backend/commands/schemacmds.c
b/src/backend/commands/schemacmds.c
index 4c51e920626..e7cb7ec898d 100644
--- a/src/backend/commands/schemacmds.c
+++ b/src/backend/commands/schemacmds.c
@@ -14,6 +14,7 @@
*/
#include "postgres.h"
+#include "access/genam.h"
#include "access/htup_details.h"
#include "access/table.h"
#include "access/xact.h"
@@ -27,17 +28,140 @@
#include "catalog/pg_namespace.h"
#include "commands/event_trigger.h"
#include "commands/schemacmds.h"
+#include "commands/tablespace.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
#include "parser/parse_utilcmd.h"
#include "parser/scansup.h"
#include "tcop/utility.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
#include "utils/syscache.h"
static void AlterSchemaOwner_internal(HeapTuple tup, Relation rel, Oid
newOwnerId);
+static List *collectSchemaTablesLike(Oid srcNspOid, const char *newSchemaName,
+ bits32
options);
+
+/*
+ * Subroutine for CREATE SCHEMA LIKE.
+ *
+ * It return a list of CreateStmt statements for tables that are on source
+ * schema that should be created on target schema.
+ *
+ * It uses CREATE TABLE ... LIKE existing infrastructure.
+ */
+static List *
+collectSchemaTablesLike(Oid srcNspOid, const char *newSchemaName,
+ bits32 options)
+{
+ List *result = NIL;
+ bool preserveIndexNames = false;
+ Relation pg_class;
+ SysScanDesc scan;
+ ScanKeyData key;
+ HeapTuple tuple;
+ bits32 tableOptions;
+
+ /*
+ * Determine CREATE TABLE LIKE options. We copy most properties by
+ * default, but indexes are controlled by the CREATE_SCHEMA_LIKE_INDEX
+ * option.
+ */
+ tableOptions = CREATE_TABLE_LIKE_COMMENTS |
+ CREATE_TABLE_LIKE_COMPRESSION |
+ CREATE_TABLE_LIKE_CONSTRAINTS |
+ CREATE_TABLE_LIKE_DEFAULTS |
+ CREATE_TABLE_LIKE_GENERATED |
+ CREATE_TABLE_LIKE_IDENTITY |
+ CREATE_TABLE_LIKE_STATISTICS |
+ CREATE_TABLE_LIKE_STORAGE;
+
+ /*
+ * If indexes are enable to be created we want to preserve the index
names
+ * for the new schema.
+ */
+ if (options & CREATE_SCHEMA_LIKE_INDEX)
+ {
+ tableOptions |= CREATE_TABLE_LIKE_INDEXES;
+ preserveIndexNames = true;
+ }
+
+ pg_class = table_open(RelationRelationId, AccessShareLock);
+
+ /*
+ * Scan the pg_class filtering relations of source schema that need to
be
+ * created on the target schema.
+ */
+ ScanKeyInit(&key,
+ Anum_pg_class_relnamespace,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(srcNspOid));
+
+ scan = systable_beginscan(pg_class, ClassNameNspIndexId, true,
+ NULL, 1, &key);
+
+ while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ {
+ Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
+ CreateStmt *createStmt;
+ TableLikeClause *likeClause;
+ RangeVar *newRelation;
+ RangeVar *sourceRelation;
+
+ /* Only process regular and partitioned tables */
+ if (classForm->relkind != RELKIND_RELATION &&
+ classForm->relkind != RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ createStmt = makeNode(CreateStmt);
+ likeClause = makeNode(TableLikeClause);
+
+ /* Target table in new schema */
+ newRelation = makeRangeVar(pstrdup(newSchemaName),
+
pstrdup(NameStr(classForm->relname)),
+ -1);
+
+ /* Source table reference */
+ sourceRelation = makeRangeVar(get_namespace_name(srcNspOid),
+
pstrdup(NameStr(classForm->relname)),
+ -1);
+
+ likeClause->relation = sourceRelation;
+ likeClause->options = tableOptions;
+ likeClause->relationOid = InvalidOid;
+ likeClause->preserveIndexNames = preserveIndexNames;
+
+ createStmt->relation = newRelation;
+ createStmt->tableElts = list_make1(likeClause);
+ createStmt->inhRelations = NIL;
+ createStmt->partbound = NULL;
+ createStmt->partspec = NULL;
+ createStmt->ofTypename = NULL;
+ createStmt->constraints = NIL;
+ createStmt->nnconstraints = NIL;
+ createStmt->options = NIL;
+ createStmt->oncommit = ONCOMMIT_NOOP;
+
+ /*
+ * XXX: Should we have INCLUDING TABLESPACE? If not, should we
use the
+ * same tablespace of source table?
+ */
+ createStmt->tablespacename = NULL;
+ createStmt->accessMethod = NULL;
+ createStmt->if_not_exists = false;
+
+
+ result = lappend(result, createStmt);
+ }
+
+ systable_endscan(scan);
+ table_close(pg_class, AccessShareLock);
+
+ return result;
+}
/*
* CREATE SCHEMA
@@ -187,6 +311,39 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char
*queryString,
EventTriggerCollectSimpleCommand(address, InvalidObjectAddress,
(Node
*) stmt);
+ /*
+ * Process LIKE clause if present. We collect objects from the source
+ * schema and append them to the schema elements list.
+ */
+ if (stmt->like_clause != NULL)
+ {
+ SchemaLikeClause *like = stmt->like_clause;
+ List *like_stmts = NIL;
+ Oid srcNspOid;
+ AclResult like_aclresult;
+
+ /* Look up source schema */
+ srcNspOid = get_namespace_oid(like->schemaname, false);
+
+ /* Check permission to read from source schema */
+ like_aclresult = object_aclcheck(NamespaceRelationId, srcNspOid,
+
GetUserId(), ACL_USAGE);
+ if (like_aclresult != ACLCHECK_OK)
+ aclcheck_error(like_aclresult, OBJECT_SCHEMA,
like->schemaname);
+
+ /* Collect tables if requested */
+ if ((like->options & CREATE_SCHEMA_LIKE_TABLE) ||
+ (like->options == CREATE_SCHEMA_LIKE_ALL))
+ {
+ like_stmts = collectSchemaTablesLike(srcNspOid,
+
schemaName,
+
like->options);
+ }
+
+ /* Append LIKE-generated statements to explicit schema elements
*/
+ stmt->schemaElts = list_concat(like_stmts, stmt->schemaElts);
+ }
+
/*
* Examine the list of commands embedded in the CREATE SCHEMA command,
and
* reorganize them into a sequentially executable order with no forward
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index f976c0e5c7e..8010fe71a40 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1301,7 +1301,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid
ownerId,
false);
idxstmt =
generateClonedIndexStmt(NULL, idxRel,
-
attmap, &constraintOid);
+
attmap, &constraintOid, false);
DefineIndex(NULL,
RelationGetRelid(rel),
idxstmt,
@@ -20771,7 +20771,7 @@ AttachPartitionEnsureIndexes(List **wqueue, Relation
rel, Relation attachrel)
stmt = generateClonedIndexStmt(NULL,
idxRel, attmap,
-
&conOid);
+
&conOid, false);
DefineIndex(NULL,
RelationGetRelid(attachrel),
stmt, InvalidOid,
RelationGetRelid(idxRel),
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 713ee5c10a2..0c23b23561d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -600,6 +600,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
%type <node> DomainConstraint TableConstraint TableLikeClause
%type <ival> TableLikeOptionList TableLikeOption
+%type <ival> SchemaLikeOptionList SchemaLikeOption
%type <str> column_compression opt_column_compression
column_storage opt_column_storage
%type <list> ColQualList
%type <node> ColConstraint ColConstraintElem ConstraintAttr
@@ -1570,6 +1571,7 @@ CreateSchemaStmt:
n->authrole = $5;
n->schemaElts = $6;
n->if_not_exists = false;
+ n->like_clause = NULL;
$$ = (Node *) n;
}
| CREATE SCHEMA ColId OptSchemaEltList
@@ -1581,6 +1583,7 @@ CreateSchemaStmt:
n->authrole = NULL;
n->schemaElts = $4;
n->if_not_exists = false;
+ n->like_clause = NULL;
$$ = (Node *) n;
}
| CREATE SCHEMA IF_P NOT EXISTS opt_single_name
AUTHORIZATION RoleSpec OptSchemaEltList
@@ -1597,6 +1600,7 @@ CreateSchemaStmt:
parser_errposition(@9)));
n->schemaElts = $9;
n->if_not_exists = true;
+ n->like_clause = NULL;
$$ = (Node *) n;
}
| CREATE SCHEMA IF_P NOT EXISTS ColId OptSchemaEltList
@@ -1613,6 +1617,40 @@ CreateSchemaStmt:
parser_errposition(@7)));
n->schemaElts = $7;
n->if_not_exists = true;
+ n->like_clause = NULL;
+ $$ = (Node *) n;
+ }
+ /* CREATE SCHEMA ... LIKE variants */
+ | CREATE SCHEMA ColId LIKE ColId SchemaLikeOptionList
+ {
+ CreateSchemaStmt *n =
makeNode(CreateSchemaStmt);
+ SchemaLikeClause *l =
makeNode(SchemaLikeClause);
+
+ n->schemaname = $3;
+ n->authrole = NULL;
+ n->schemaElts = NIL;
+ n->if_not_exists = false;
+
+ l->schemaname = $5;
+ l->options = $6;
+ n->like_clause = l;
+
+ $$ = (Node *) n;
+ }
+ | CREATE SCHEMA IF_P NOT EXISTS ColId LIKE ColId
SchemaLikeOptionList
+ {
+ CreateSchemaStmt *n =
makeNode(CreateSchemaStmt);
+ SchemaLikeClause *l =
makeNode(SchemaLikeClause);
+
+ n->schemaname = $6;
+ n->authrole = NULL;
+ n->schemaElts = NIL;
+ n->if_not_exists = true;
+
+ l->schemaname = $8;
+ l->options = $9;
+ n->like_clause = l;
+
$$ = (Node *) n;
}
;
@@ -1639,6 +1677,21 @@ schema_stmt:
| ViewStmt
;
+/*
+ * Options for CREATE SCHEMA ... LIKE
+ */
+SchemaLikeOptionList:
+ SchemaLikeOptionList INCLUDING SchemaLikeOption
{ $$ = $1 | $3; }
+ | SchemaLikeOptionList EXCLUDING SchemaLikeOption
{ $$ = $1 & ~$3; }
+ | /* EMPTY */
{ $$ = 0; }
+ ;
+
+SchemaLikeOption:
+ TABLE { $$ =
CREATE_SCHEMA_LIKE_TABLE; }
+ | INDEX { $$ =
CREATE_SCHEMA_LIKE_INDEX; }
+ | ALL { $$ = CREATE_SCHEMA_LIKE_ALL; }
+ ;
+
/*****************************************************************************
*
diff --git a/src/backend/parser/parse_utilcmd.c
b/src/backend/parser/parse_utilcmd.c
index b5f4c72459d..b3188d87209 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1567,7 +1567,8 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause
*table_like_clause)
index_stmt = generateClonedIndexStmt(heapRel,
parent_index,
attmap,
-
NULL);
+
NULL,
+
table_like_clause->preserveIndexNames);
/* Copy comment on index, if requested */
if (table_like_clause->options &
CREATE_TABLE_LIKE_COMMENTS)
@@ -1694,7 +1695,8 @@ transformOfType(CreateStmtContext *cxt, TypeName
*ofTypename)
IndexStmt *
generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
const AttrMap *attmap,
- Oid *constraintOid)
+ Oid *constraintOid,
+ bool preserveIndexName)
{
Oid source_relid = RelationGetRelid(source_idx);
HeapTuple ht_idxrel;
@@ -1771,12 +1773,19 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation
source_idx,
index->reset_default_tblspc = false;
/*
- * We don't try to preserve the name of the source index; instead, just
+ * By default, we don't preserve the name of the source index; instead,
* let DefineIndex() choose a reasonable name. (If we tried to preserve
* the name, we'd get duplicate-relation-name failures unless the source
* table was in a different schema.)
+ *
+ * However, when preserveIndexName is true (e.g., when copying tables
+ * between schemas via CREATE SCHEMA ... LIKE), we preserve the original
+ * name since it won't conflict in the different namespace.
*/
- index->idxname = NULL;
+ if (preserveIndexName)
+ index->idxname = pstrdup(NameStr(idxrelrec->relname));
+ else
+ index->idxname = NULL;
/*
* If the index is marked PRIMARY or has an exclusion condition, it's
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 646d6ced763..2171c778f84 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -782,6 +782,8 @@ typedef struct TableLikeClause
RangeVar *relation;
bits32 options; /* OR of TableLikeOption flags
*/
Oid relationOid; /* If table has been looked up,
its OID */
+ bool preserveIndexNames; /* true if index name should be
preserved
+ * for
the new table */
} TableLikeClause;
typedef enum TableLikeOption
@@ -2411,6 +2413,20 @@ typedef enum ObjectType
* executed after the schema itself is created.
* ----------------------
*/
+typedef struct SchemaLikeClause
+{
+ NodeTag type;
+ char *schemaname; /* source schema name */
+ bits32 options; /* OR of SchemaLikeOption flags
*/
+} SchemaLikeClause;
+
+typedef enum SchemaLikeOption
+{
+ CREATE_SCHEMA_LIKE_TABLE = 1 << 0,
+ CREATE_SCHEMA_LIKE_INDEX = 1 << 1,
+ CREATE_SCHEMA_LIKE_ALL = PG_INT32_MAX
+} SchemaLikeOption;
+
typedef struct CreateSchemaStmt
{
NodeTag type;
@@ -2418,6 +2434,7 @@ typedef struct CreateSchemaStmt
RoleSpec *authrole; /* the owner of the created schema */
List *schemaElts; /* schema components (list of
parsenodes) */
bool if_not_exists; /* just do nothing if schema already
exists? */
+ SchemaLikeClause *like_clause; /* LIKE clause if present, else NULL */
} CreateSchemaStmt;
typedef enum DropBehavior
diff --git a/src/include/parser/parse_utilcmd.h
b/src/include/parser/parse_utilcmd.h
index 53f282f77ec..6841d7b16bc 100644
--- a/src/include/parser/parse_utilcmd.h
+++ b/src/include/parser/parse_utilcmd.h
@@ -39,6 +39,7 @@ extern List *expandTableLikeClause(RangeVar *heapRel,
extern IndexStmt *generateClonedIndexStmt(RangeVar *heapRel,
Relation source_idx,
const AttrMap *attmap,
-
Oid *constraintOid);
+
Oid *constraintOid,
+
bool preserveIndexName);
#endif /* PARSE_UTILCMD_H */
diff --git a/src/test/regress/expected/create_schema.out
b/src/test/regress/expected/create_schema.out
index 93302a07efc..f2760245f0c 100644
--- a/src/test/regress/expected/create_schema.out
+++ b/src/test/regress/expected/create_schema.out
@@ -94,5 +94,114 @@ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
DROP SCHEMA regress_schema_1 CASCADE;
NOTICE: drop cascades to table regress_schema_1.tab
RESET ROLE;
+--
+-- CREATE SCHEMA ... LIKE tests
+--
+-- Create a source schema with various objects
+CREATE SCHEMA regress_source_schema;
+CREATE TABLE regress_source_schema.t1 (
+ id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+ name text NOT NULL,
+ created_at timestamp DEFAULT now()
+);
+CREATE TABLE regress_source_schema.t2 (
+ id int REFERENCES regress_source_schema.t1(id),
+ data jsonb
+);
+CREATE INDEX idx_t1_name ON regress_source_schema.t1(name);
+CREATE INDEX idx_t2_data ON regress_source_schema.t2 USING gin(data);
+-- Test basic LIKE with TABLE
+CREATE SCHEMA regress_copy1 LIKE regress_source_schema INCLUDING TABLE;
+-- Verify tables were copied
+SELECT table_name FROM information_schema.tables
+WHERE table_schema = 'regress_copy1' ORDER BY table_name;
+ table_name
+------------
+ t1
+ t2
+(2 rows)
+
+-- Verify table structure (should have columns but not indexes)
+\d regress_copy1.t1
+ Table "regress_copy1.t1"
+ Column | Type | Collation | Nullable |
Default
+------------+-----------------------------+-----------+----------+------------------------------
+ id | integer | | not null | generated
always as identity
+ name | text | | not null |
+ created_at | timestamp without time zone | | | now()
+
+-- Test LIKE with TABLE and INDEX
+CREATE SCHEMA regress_copy2 LIKE regress_source_schema INCLUDING TABLE
INCLUDING INDEX;
+-- Verify indexes were copied (the name should be the same)
+SELECT indexname FROM pg_indexes WHERE schemaname = 'regress_copy2' ORDER BY
indexname;
+ indexname
+-------------
+ idx_t1_name
+ idx_t2_data
+ t1_pkey
+(3 rows)
+
+-- Verify table structure (should have columns and indexes)
+\d regress_copy2.t1
+ Table "regress_copy2.t1"
+ Column | Type | Collation | Nullable |
Default
+------------+-----------------------------+-----------+----------+------------------------------
+ id | integer | | not null | generated
always as identity
+ name | text | | not null |
+ created_at | timestamp without time zone | | | now()
+Indexes:
+ "t1_pkey" PRIMARY KEY, btree (id)
+ "idx_t1_name" btree (name)
+
+-- Test EXCLUDING option
+CREATE SCHEMA regress_copy3 LIKE regress_source_schema INCLUDING ALL EXCLUDING
INDEX;
+-- Should have tables but no indexes
+SELECT table_name FROM information_schema.tables
+WHERE table_schema = 'regress_copy3' ORDER BY table_name;
+ table_name
+------------
+ t1
+ t2
+(2 rows)
+
+SELECT indexname FROM pg_indexes WHERE schemaname = 'regress_copy3' ORDER BY
indexname;
+ indexname
+-----------
+(0 rows)
+
+-- Test IF NOT EXISTS with LIKE
+CREATE SCHEMA IF NOT EXISTS regress_copy1 LIKE regress_source_schema INCLUDING
TABLE;
+NOTICE: schema "regress_copy1" already exists, skipping
+-- Test empty source schema
+CREATE SCHEMA regress_empty_source;
+CREATE SCHEMA regress_copy4 LIKE regress_empty_source INCLUDING ALL;
+SELECT table_name FROM information_schema.tables
+WHERE table_schema = 'regress_copy4' ORDER BY table_name;
+ table_name
+------------
+(0 rows)
+
+-- Test source schema does not exist
+CREATE SCHEMA regress_copy_fail LIKE nonexistent_schema INCLUDING TABLE;
+ERROR: schema "nonexistent_schema" does not exist
+-- Clean up LIKE tests
+DROP SCHEMA regress_copy1 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table regress_copy1.t1
+drop cascades to table regress_copy1.t2
+DROP SCHEMA regress_copy2 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table regress_copy2.t1
+drop cascades to table regress_copy2.t2
+DROP SCHEMA regress_copy3 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table regress_copy3.t1
+drop cascades to table regress_copy3.t2
+DROP SCHEMA regress_copy4 CASCADE;
+DROP SCHEMA regress_empty_source CASCADE;
+DROP SCHEMA regress_source_schema CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table regress_source_schema.t1
+drop cascades to table regress_source_schema.t2
-- Clean up
DROP ROLE regress_create_schema_role;
diff --git a/src/test/regress/sql/create_schema.sql
b/src/test/regress/sql/create_schema.sql
index 1b7064247a1..39a35e95150 100644
--- a/src/test/regress/sql/create_schema.sql
+++ b/src/test/regress/sql/create_schema.sql
@@ -66,5 +66,73 @@ CREATE SCHEMA regress_schema_1 AUTHORIZATION CURRENT_ROLE
DROP SCHEMA regress_schema_1 CASCADE;
RESET ROLE;
+--
+-- CREATE SCHEMA ... LIKE tests
+--
+
+-- Create a source schema with various objects
+CREATE SCHEMA regress_source_schema;
+
+CREATE TABLE regress_source_schema.t1 (
+ id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+ name text NOT NULL,
+ created_at timestamp DEFAULT now()
+);
+
+CREATE TABLE regress_source_schema.t2 (
+ id int REFERENCES regress_source_schema.t1(id),
+ data jsonb
+);
+
+CREATE INDEX idx_t1_name ON regress_source_schema.t1(name);
+CREATE INDEX idx_t2_data ON regress_source_schema.t2 USING gin(data);
+
+-- Test basic LIKE with TABLE
+CREATE SCHEMA regress_copy1 LIKE regress_source_schema INCLUDING TABLE;
+
+-- Verify tables were copied
+SELECT table_name FROM information_schema.tables
+WHERE table_schema = 'regress_copy1' ORDER BY table_name;
+
+-- Verify table structure (should have columns but not indexes)
+\d regress_copy1.t1
+
+-- Test LIKE with TABLE and INDEX
+CREATE SCHEMA regress_copy2 LIKE regress_source_schema INCLUDING TABLE
INCLUDING INDEX;
+
+-- Verify indexes were copied (the name should be the same)
+SELECT indexname FROM pg_indexes WHERE schemaname = 'regress_copy2' ORDER BY
indexname;
+
+-- Verify table structure (should have columns and indexes)
+\d regress_copy2.t1
+
+-- Test EXCLUDING option
+CREATE SCHEMA regress_copy3 LIKE regress_source_schema INCLUDING ALL EXCLUDING
INDEX;
+
+-- Should have tables but no indexes
+SELECT table_name FROM information_schema.tables
+WHERE table_schema = 'regress_copy3' ORDER BY table_name;
+SELECT indexname FROM pg_indexes WHERE schemaname = 'regress_copy3' ORDER BY
indexname;
+
+-- Test IF NOT EXISTS with LIKE
+CREATE SCHEMA IF NOT EXISTS regress_copy1 LIKE regress_source_schema INCLUDING
TABLE;
+
+-- Test empty source schema
+CREATE SCHEMA regress_empty_source;
+CREATE SCHEMA regress_copy4 LIKE regress_empty_source INCLUDING ALL;
+SELECT table_name FROM information_schema.tables
+WHERE table_schema = 'regress_copy4' ORDER BY table_name;
+
+-- Test source schema does not exist
+CREATE SCHEMA regress_copy_fail LIKE nonexistent_schema INCLUDING TABLE;
+
+-- Clean up LIKE tests
+DROP SCHEMA regress_copy1 CASCADE;
+DROP SCHEMA regress_copy2 CASCADE;
+DROP SCHEMA regress_copy3 CASCADE;
+DROP SCHEMA regress_copy4 CASCADE;
+DROP SCHEMA regress_empty_source CASCADE;
+DROP SCHEMA regress_source_schema CASCADE;
+
-- Clean up
DROP ROLE regress_create_schema_role;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 7619845fba9..cf86b26558d 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2716,6 +2716,7 @@ ScanKeywordList
ScanState
ScanTypeControl
ScannerCallbackState
+SchemaLikeClause
SchemaQuery
SearchPathCacheEntry
SearchPathCacheKey
--
2.52.0
