Add CREATE SCHEMA ... LIKE support

Hi Matheus

On 06/02/2026 15:22, Matheus Alcantara wrote:

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 generally like the idea.

One quick note:

I'm just wondering if using a similar syntax as IMPORT FOREIGN SCHEMA
would be better than creating a new one

IMPORT FOREIGN SCHEMA remote_schema
[ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
FROM SERVER server_name
INTO local_schema
[ OPTIONS ( option 'value' [, ... ] ) ]

So EXCEPT instead of EXCLUDING and LIMIT TO instead of INCLUDING. Of
course, assuming the proposed syntax isn't already defined in the SQL
standard.

Best, Jim

On 06/02/26 11:35, Jim Jones wrote:

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 generally like the idea.

One quick note:

I'm just wondering if using a similar syntax as IMPORT FOREIGN SCHEMA
would be better than creating a new one

IMPORT FOREIGN SCHEMA remote_schema
[ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
FROM SERVER server_name
INTO local_schema
[ OPTIONS ( option 'value' [, ... ] ) ]

So EXCEPT instead of EXCLUDING and LIMIT TO instead of INCLUDING. Of
course, assuming the proposed syntax isn't already defined in the SQL
standard.

Yeah, it could be an option but IMHO adding the LIKE on CREATE SCHEMA
sounds more similar to what we already have for CREATE TABLE ... LIKE.

Although I prefer the CREATE SCHEMA LIKE syntax I'm open to discuss
about other syntaxes.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

Em sex., 6 de fev. de 2026 às 11:22, Matheus Alcantara <
matheusssilv97@gmail.com> escreveu:

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...

This feature is really cool.
Having a function to clone schemas is what we use for these cases, so it
would be very interesting to have a command that replaces that function.
One observation is that you are using CREATE TABLE ... LIKE and this
command does not respect some object names, like indexes and constraints.
Your example idx_t1_name will clone as t1_name_idx

So the cloned schema will not be exactly the same as the source.
Maybe a rename process to match names from source to target next to CREATE
TABLE LIKE would solve those cases.

regards
Marcos

On 06/02/2026 15:42, Matheus Alcantara wrote:

On 06/02/26 11:35, Jim Jones wrote:

So EXCEPT instead of EXCLUDING and LIMIT TO instead of INCLUDING. Of
course, assuming the proposed syntax isn't already defined in the SQL
standard.

Yeah, it could be an option but IMHO adding the LIKE on CREATE SCHEMA
sounds more similar to what we already have for CREATE TABLE ... LIKE.

Although I prefer the CREATE SCHEMA LIKE syntax I'm open to discuss
about other syntaxes.

Just to be clear, I also like the CREATE SCHEMA <name> LIKE
<source_schema> syntax. I was just suggesting to consider changing
EXCLUDING and INCLUDING to EXCEPT and LIMIT TO, respectively. Just to
make it more consistent with IMPORT FOREIGN SCHEMA, although I find
INCLUDING and EXCLUDING way more intuitive :)

Best, Jim

On Friday, February 6, 2026, Matheus Alcantara <matheusssilv97@gmail.com>
wrote:

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.

I’d suggest you solve the functions cloning first to make sure that won’t
end up being a blocker. SET clauses in particular, and “atomic” functions,
seem non-trivial to deal with. Views too really, depending on the approach.

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).

David J.

On Fri, Feb 06, 2026 at 04:23:45PM +0100, Jim Jones wrote:

On 06/02/2026 15:42, Matheus Alcantara wrote:

On 06/02/26 11:35, Jim Jones wrote:

So EXCEPT instead of EXCLUDING and LIMIT TO instead of INCLUDING. Of
course, assuming the proposed syntax isn't already defined in the SQL
standard.

Yeah, it could be an option but IMHO adding the LIKE on CREATE SCHEMA
sounds more similar to what we already have for CREATE TABLE ... LIKE.

Although I prefer the CREATE SCHEMA LIKE syntax I'm open to discuss
about other syntaxes.

Just to be clear, I also like the CREATE SCHEMA <name> LIKE
<source_schema> syntax. I was just suggesting to consider changing
EXCLUDING and INCLUDING to EXCEPT and LIMIT TO, respectively. Just to
make it more consistent with IMPORT FOREIGN SCHEMA, although I find
INCLUDING and EXCLUDING way more intuitive :)

The way I see it EXCLUDING and INCLUDING are for TYPES of objects (like:
EXCLUDING indexes), while EXCEPT and LIMIT TO are for specific objects
(EXCEPT users).

Best regards,

depesz

On 06/02/26 12:19, Marcos Pegoraro wrote:

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...

This feature is really cool.
Having a function to clone schemas is what we use for these cases, so it
would be very interesting to have a command that replaces that function.
One observation is that you are using CREATE TABLE ... LIKE and this
command does not respect some object names, like indexes and constraints.
Your example idx_t1_name will clone as t1_name_idx

So the cloned schema will not be exactly the same as the source.
Maybe a rename process to match names from source to target next to CREATE
TABLE LIKE would solve those cases.

Thanks for the input. I'll think about how to fix this for the next
version.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

On 06/02/26 12:30, David G. Johnston wrote:

On Friday, February 6, 2026, Matheus Alcantara <matheusssilv97@gmail.com>
wrote:

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.

I’d suggest you solve the functions cloning first to make sure that won’t
end up being a blocker. SET clauses in particular, and “atomic” functions,
seem non-trivial to deal with. Views too really, depending on the approach.

Thanks for the call. I'll focus on these for the next version.

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.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

On 06/02/26 12:23, Jim Jones wrote:

On 06/02/2026 15:42, Matheus Alcantara wrote:

On 06/02/26 11:35, Jim Jones wrote:

So EXCEPT instead of EXCLUDING and LIMIT TO instead of INCLUDING. Of
course, assuming the proposed syntax isn't already defined in the SQL
standard.

Yeah, it could be an option but IMHO adding the LIKE on CREATE SCHEMA
sounds more similar to what we already have for CREATE TABLE ... LIKE.

Although I prefer the CREATE SCHEMA LIKE syntax I'm open to discuss
about other syntaxes.

Just to be clear, I also like the CREATE SCHEMA <name> LIKE
<source_schema> syntax. I was just suggesting to consider changing
EXCLUDING and INCLUDING to EXCEPT and LIMIT TO, respectively. Just to
make it more consistent with IMPORT FOREIGN SCHEMA, although I find
INCLUDING and EXCLUDING way more intuitive :)

The idea of INCLUDING and EXCLUDING is to specify objects that should
be included/excluded, e.g INCLUDING ALL EXCLUDING INDEX EXCLUDING VIEW

It seems to me that LIMIT TO and EXCEPT is more related to specify
which objects you want to include/exclude e.g "EXCEPT users" (where
users is a table)

(While I'm writing this email, Hubert Depesz has shared a similar
comment about this [1]/messages/by-id/aYYKQDln_qjA_CdA@depesz.com)

[1]: /messages/by-id/aYYKQDln_qjA_CdA@depesz.com

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

On 06/02/2026 16:52, Matheus Alcantara wrote:

The idea of INCLUDING and EXCLUDING is to specify objects that should
be included/excluded, e.g INCLUDING ALL EXCLUDING INDEX EXCLUDING VIEW

It seems to me that LIMIT TO and EXCEPT is more related to specify
which objects you want to include/exclude e.g "EXCEPT users" (where
users is a table)

Oh, I see it now. It means including/excluding all tables, not specific
ones. In that case I also agree that LIMIT TO and EXCEPT are not the
best fit here :)

Best, Jim

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

Em seg., 9 de fev. de 2026 às 14:42, Matheus Alcantara <
matheusssilv97@gmail.com> escreveu:

So here is V2 with some documentation changes and also with the index
name not being preserved issue that Marcos have mentioned earlier fixed.

You followed INCLUDING and EXCLUDING as CREATE TABLE LIKE does, but the
problem is that on command CREATE TABLE LIKE if you EXCLUDE any of
available options, which are: COMMENTS | COMPRESSION | CONSTRAINTS |
DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE, the table
will be created anyway, because none of them are obligatory
Now you are creating several objects and some of them are dependent, what
do you do if a table depends on a TYPE or a DOMAIN but they were not
included on the options list ? And more, a different TYPE or DOMAIN with
that name exists but on another schema that is in the search_path ?
I think only CREATE TABLE LIKE like you did will not work as expected.
Imagine something like this.

set search_path to public;
create domain i32 integer check (value > 0);
create schema a;
create table a.t1(id i32);
--create schema like should get an exception because a table cannot be
created without the domains it depends.
create schema b like a excluding domain;
--then a second problem
set search_path to a;
--create a second domain but same name. table a continues using public.i32.
create domain i32 integer check (value = 1);
--now we have two different domains, which on will be used ?
--create schema like would get an error because domain was not found on
search_path or would create a table using a wrong object ?
create schema b like a including all;

regards
Marcos

Em seg., 9 de fev. de 2026 às 14:42, Matheus Alcantara <
matheusssilv97@gmail.com> escreveu:

So here is V2 with some documentation changes and also with the index
name not being preserved issue that Marcos have mentioned earlier fixed.

And additionally

Default is EXCLUDING ALL, right ?
create schema b like a;
It's the same as
create schema b;
right ?

regards
Marcos

On 09/02/26 18:44, Marcos Pegoraro wrote:

So here is V2 with some documentation changes and also with the index
name not being preserved issue that Marcos have mentioned earlier fixed.

And additionally

Default is EXCLUDING ALL, right ?

Yes

create schema b like a;
It's the same as
create schema b;
right ?
Yes too.

Thanks for checking this patch. I'm preparing a reply for your
previous comments.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

On 09/02/26 17:53, Marcos Pegoraro wrote:

So here is V2 with some documentation changes and also with the index
name not being preserved issue that Marcos have mentioned earlier fixed.

You followed INCLUDING and EXCLUDING as CREATE TABLE LIKE does, but the
problem is that on command CREATE TABLE LIKE if you EXCLUDE any of
available options, which are: COMMENTS | COMPRESSION | CONSTRAINTS |
DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE, the table
will be created anyway, because none of them are obligatory
Now you are creating several objects and some of them are dependent, what
do you do if a table depends on a TYPE or a DOMAIN but they were not
included on the options list ?

I think that the simple way would to just let the error happen when
creating the table. Perhaps we could add a HINT to mention that e.g
INCLUDING TABLE EXCLUDING TYPE will not work if a table depends of a
specific created type.

Another way would be to automatic create these dependencies and not
have options like TYPE or DOMAIN for example, but I think that this
can limitless the LIKE options that we could have.

And more, a different TYPE or DOMAIN with
that name exists but on another schema that is in the search_path ?
I think only CREATE TABLE LIKE like you did will not work as expected.
Imagine something like this.

set search_path to public;
create domain i32 integer check (value > 0);
create schema a;
create table a.t1(id i32);
--create schema like should get an exception because a table cannot be
created without the domains it depends.
create schema b like a excluding domain;

(I think that the command would be something like create schema b like
a including all excluding domain)

This seems right to me. Exclude a domain if you have a table that
depend on it should throw an error. It seems to me that DOMAIN and
TYPE is more likely to be used with INCLUDING (e.g CREATE SCHEMA b
LIKE a INCLUDING TYPE INCLUDING DOMAIN) or I'm missing something?

One idea would be to have something like LIMIT TO and EXPECT to select
only some tables to include/exclude.

--then a second problem
set search_path to a;
--create a second domain but same name. table a continues using public.i32.
create domain i32 integer check (value = 1);
--now we have two different domains, which on will be used ?
--create schema like would get an error because domain was not found on
search_path or would create a table using a wrong object ?
create schema b like a including all;

It will still reference public.i32 since it is what a.t1 is
referencing. I think that we should only recreate the objects from the
source schema, so public.i32 should still be used on table b.t1. If
a.t1.id is changed to reference a.i32, so b.i32 should also be created
when running create schema b like a including all;

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

On 09/02/2026 23:39, Matheus Alcantara wrote:

So here is V2 with some documentation changes and also with the index
name not being preserved issue that Marcos have mentioned earlier fixed.

Thanks for the patch!

One observation:

In a scenario where a parent table and the partitioned tables live in
different schemas, creating a schema based on the schema that contains
only the partitions arguably generates useless tables.

postgres=# CREATE SCHEMA s1;
CREATE TABLE s1.m (
city_id int NOT NULL,
logdate date NOT NULL
) PARTITION BY RANGE (logdate);
CREATE SCHEMA
CREATE TABLE

Partitions are in s2:

postgres=# CREATE SCHEMA s2;
CREATE TABLE s2.p1 PARTITION OF s1.m
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE s2.p2 PARTITION OF s1.m
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
CREATE SCHEMA
CREATE TABLE
CREATE TABLE

postgres=# CREATE SCHEMA s3 LIKE s2 INCLUDING ALL;
CREATE SCHEMA
postgres=# \d s3.p1
Table "s3.p1"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
city_id | integer | | not null |
logdate | date | | not null |

postgres=# \d s2.p1
Table "s2.p1"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
city_id | integer | | not null |
logdate | date | | not null |
Partition of: s1.m FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')

I'm not saying it's wrong, but perhaps you should consider ERROR/WARN if
trying to copy a schema with "orphan" partitions

The same applies for creating schema that contains only the parent table

postgres=# CREATE SCHEMA s4 LIKE s1 INCLUDING ALL;
CREATE SCHEMA
postgres=# \d s4.m
Table "s4.m"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
city_id | integer | | not null |
logdate | date | | not null |

postgres=# \d s1.m
Partitioned table "s1.m"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
city_id | integer | | not null |
logdate | date | | not null |
Partition key: RANGE (logdate)
Number of partitions: 2 (Use \d+ to list them.)

Even if parent and children live in the same schema, they become
detached in the new copy -- I'd argue that this one is a bug.

postgres=# CREATE SCHEMA s1;
CREATE TABLE s1.m (
city_id int NOT NULL,
logdate date NOT NULL
) PARTITION BY RANGE (logdate);
CREATE SCHEMA
CREATE TABLE

postgres=# CREATE TABLE s1.p1 PARTITION OF s1.m
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE s1.p2 PARTITION OF s1.m
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
CREATE TABLE
CREATE TABLE

postgres=# CREATE SCHEMA s2 LIKE s1 INCLUDING ALL;
CREATE SCHEMA

postgres=# \d s1.m
Partitioned table "s1.m"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
city_id | integer | | not null |
logdate | date | | not null |
Partition key: RANGE (logdate)
Number of partitions: 2 (Use \d+ to list them.)

postgres=# \d s2.m
Table "s2.m"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
city_id | integer | | not null |
logdate | date | | not null |

postgres=# \d s1.p1
Table "s1.p1"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
city_id | integer | | not null |
logdate | date | | not null |
Partition of: s1.m FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')

postgres=# \d s2.p1
Table "s2.p1"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
city_id | integer | | not null |
logdate | date | | not null |

Comments are also being ignored, but I guess it was already mentioned
upthread:

postgres=# \dt+ s2.t
List of tables
-[ RECORD 1 ]-+----------
Schema | s2
Name | t
Type | table
Owner | jim
Persistence | permanent
Access method | heap
Size | 0 bytes
Description |

postgres=# \dt+ s1.t
List of tables
-[ RECORD 1 ]-+----------
Schema | s1
Name | t
Type | table
Owner | jim
Persistence | permanent
Access method | heap
Size | 0 bytes
Description | foo

Thanks!

Best, Jim

On 10/02/2026 11:09, Jim Jones wrote:

In a scenario where a parent table and the partitioned tables live in
different schemas, creating a schema based on the schema that contains
only the partitions arguably generates useless tables.

The same applies for creating schema that contains only the parent table

Even if parent and children live in the same schema, they become
detached in the new copy -- I'd argue that this one is a bug.

Comments are also being ignored, but I guess it was already mentioned
upthread:

I also just noticed that UNLOGGED tables are cloned as normal tables:

postgres=# CREATE SCHEMA s1;
CREATE UNLOGGED TABLE s1.t(c int);
CREATE SCHEMA s2 LIKE s1 INCLUDING ALL;
CREATE SCHEMA
CREATE TABLE
CREATE SCHEMA

postgres=# \d s1.t
Unlogged table "s1.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c | integer | | |

postgres=# \d s2.t
Table "s2.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c | integer | | |

Adding this to collectSchemaTablesLike in schemacmds.c could do the trick:

newRelation->relpersistence = classForm->relpersistence;

Best, Jim

Em ter., 10 de fev. de 2026 às 07:09, Jim Jones <jim.jones@uni-muenster.de>
escreveu:

Even if parent and children live in the same schema, they become
detached in the new copy -- I'd argue that this one is a bug.

I think the approach will fail at some point if you keep using CREATE TABLE
LIKE, because so many things will be incomplete or at least strange, that
you'll have to redo many of them.
Sequence values will be shared with the old table.
Constraints/Indexes names will have to be renamed.
Partitioned tables will have to be attached.
Foreign Keys are not created either.

These are the ones we've found so far, but other inconsistencies will
certainly appear.
So I don't know if CREATE TABLE LIKE is the best way to do this work.

regards
Marcos

On 10/02/2026 12:52, Marcos Pegoraro wrote:

I think the approach will fail at some point if you keep using CREATE
TABLE LIKE, because so many things will be incomplete or at least
strange, that you'll have to redo many of them.
Sequencevalues will be shared with the old table.
Constraints/Indexes names will have to be renamed.
Partitioned tables will have to be attached.
Foreign Keys are not created either.

Quite right. Either we draw a clear line here, or it will be a tough nut
to crack.

Best, Jim