Hi hackers
I was reading the TODO wiki page and found the "Add CREATE SCHEMA ...
LIKE that copies a schema" item and I thought that it could be a good
idea to have this at core.
It's common for certain applications to have a different schema for each
customer and having a built-in way to copy an entire schema could be
useful for this use case.
I'd like to propose the introduction of the LIKE syntax on CREATE SCHEMA
command with the following syntax:
CREATE SCHEMA <name> LIKE <source_schema> [like_options...]
Where like_options is:
{ INCLUDING | EXCLUDING } { TABLE | INDEX | ... | ALL }
The idea of LIKE syntax is to create a new schema with all objects that
exist on source schema like tables, indexes, sequences, functions,
views, etc.
I'm attaching a PoC patch that implements the LIKE syntax for tables and
indexes. I would like to start a discussion to see if this feature make
sense and if it could be useful to have at core. I have intention to add
support for more objects(sequence, functions, ...) in future patches if
It makes sense.
Thoughts?
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
From 982d08913d355a64dca88c0a933285d953cd970c Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Tue, 3 Feb 2026 17:57:47 -0300
Subject: [PATCH v1] 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.
---
src/backend/commands/schemacmds.c | 147 ++++++++++++++++++++
src/backend/parser/gram.y | 53 +++++++
src/include/nodes/parsenodes.h | 15 ++
src/test/regress/expected/create_schema.out | 109 +++++++++++++++
src/test/regress/sql/create_schema.sql | 68 +++++++++
src/tools/pgindent/typedefs.list | 1 +
6 files changed, 393 insertions(+)
diff --git a/src/backend/commands/schemacmds.c
b/src/backend/commands/schemacmds.c
index 4c51e920626..84a9eb33c1e 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,130 @@
#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;
+ 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 (options & CREATE_SCHEMA_LIKE_INDEX)
+ tableOptions |= CREATE_TABLE_LIKE_INDEXES;
+
+ 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;
+
+ 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 +301,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/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/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 646d6ced763..60444006443 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2411,6 +2411,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 +2432,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/test/regress/expected/create_schema.out
b/src/test/regress/expected/create_schema.out
index 93302a07efc..d5a5b55e08c 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
+SELECT indexname FROM pg_indexes WHERE schemaname = 'regress_copy2' ORDER BY
indexname;
+ indexname
+-------------
+ t1_name_idx
+ t1_pkey
+ t2_data_idx
+(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)
+ "t1_name_idx" 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..6c0b40212ae 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
+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 9f5ee8fd482..9a05319163b 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2715,6 +2715,7 @@ ScanKeywordList
ScanState
ScanTypeControl
ScannerCallbackState
+SchemaLikeClause
SchemaQuery
SearchPathCacheEntry
SearchPathCacheKey
--
2.52.0
