Re: Add CREATE SCHEMA ... LIKE support

On 11/02/2026 21:24, Matheus Alcantara wrote:
> I've fixed this by adding a WARNING message and skipping the table
> partition.

Nice! The system now issues a WARNING if the parent table lives in a
different schema

CREATE SCHEMA s3 LIKE s2 INCLUDING ALL;
WARNING: skipping partition "s2.p1" because its parent table is in
schema "s1"

I'm wondering if the same should apply for a schema containing only the
parent table. Currently, it creates a partitioned table with 0 partitions:

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

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 s3 LIKE s1 INCLUDING ALL;

\d s3.m
Partitioned table "s3.m"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
city_id | integer | | not null |
logdate | date | | not null |
Partition key: RANGE (logdate)
Number of partitions: 0

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

> On this new version of the patch I've also added support for FK's and it
> has the same behaviour, if a FK reference a table outside from the
> source schema the FK will not be created.
>
> I think that any object that reference an object outside of the source
> schema should be skipped. It fells more safe to me to avoid sharing the
> same object by multiple schemas.

Foreign keys also seem to work now:

CREATE SCHEMA s1;
CREATE TABLE s1.p (
id int PRIMARY KEY,
txt text NOT NULL
);
CREATE TABLE s1.c (
id int PRIMARY KEY,
p_id int REFERENCES s1.p(id)
);

CREATE SCHEMA s2 LIKE s1 INCLUDING ALL;

\d s2.p
Table "s2.p"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
txt | text | | not null |
Indexes:
"p_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "s2.c" CONSTRAINT "c_p_id_fkey" FOREIGN KEY (p_id) REFERENCES
s2.p(id)

\d s2.c
Table "s2.c"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
p_id | integer | | |
Indexes:
"c_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"c_p_id_fkey" FOREIGN KEY (p_id) REFERENCES s2.p(id)

A WARNING is now issued if a foreign key references to a table in a
different schema than the one being used as a template:

CREATE SCHEMA s1;
CREATE TABLE s1.p (
id int PRIMARY KEY,
txt text NOT NULL
);
CREATE SCHEMA s2;
CREATE TABLE s2.c (
id int PRIMARY KEY,
p_id int REFERENCES s1.p(id)
);
CREATE SCHEMA s3 LIKE s2 INCLUDING ALL;

WARNING: skipping foreign key "c_p_id_fkey" on table "s2.c" because it
references table "s1.p" in a different schema

\d s3.c
Table "s3.c"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
p_id | integer | | |
Indexes:
"c_pkey" PRIMARY KEY, btree (id)

>
>> 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.
>>
>
> I've added support for partitioned tables on this new version. Although
> this works for the test cases that I've added on create_schema.sql (make
> check is also happy) I'm not sure if it's the best way to do it. On
> getPartitionBoundSpec() I get PartitionBoundSpec from a given partitiond
> oid as a string and then call stringToNode to actually generate a
> PartitionBoundSpec. The problem IIUC is that fields like lowerdatums,
> upperdatums and listdatums are already transformed so when
> transformPartitionBoundValue() call transformExpr() the Node* will
> already be transformed (e.g PartitionRangeDatum vs T_A_Const) and it
> will fail on switch (nodeTag(Node))

Schemas containing both parent and children are now copied as expected.

CREATE SCHEMA s1;

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

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 SCHEMA s2 LIKE s1 INCLUDING ALL;

\d s2.m
Partitioned table "s2.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.)

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

> I fixed this by adding a is_transformed field on PartitionBoundSpec and
> set it to true on getPartitionBoundSpec(). Hash partition bounds only
> have int values (modulus and remainder) and it shows to be required to
> still do the transformation for this case.
>
> When I was writing this email I've noticed that PartitionSpec need the
> same workaround for partition keys that are expressions. I want to study
> more the code to understand how we could properly fix this. Any idea is
> welcome.
>
>> Comments are also being ignored, but I guess it was already mentioned
>> upthread:> It should work... I'll investigate this.
I believe it's an unrelated bug at expandTableLikeClause(). I opened a
new thread for this:

https://www.postgresql.org/message-id/e08cb97f-0364-4002-9cda-3c16b42e4136%40uni-muenster.de

>> I also just noticed that UNLOGGED tables are cloned as normal tables:
>> ...
>> Adding this to collectSchemaTablesLike in schemacmds.c could do the trick:
>> newRelation->relpersistence = classForm->relpersistence;
>>
>
> I've also fixed this.

Nice. It now also works!

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

\d s2.t
Unlogged table "s2.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c | integer | | |

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

> I also want to mention that I don't think that we would be able to
> properly re-created 100% all objects from the source schema into the new
> schema. Some objects will be hard to copy and can still generate bougy
> objects like functions for example as David mention on [1] (we can
> support some kind of functions but some others will be hard).

Yeah. I also think we need to draw a line at some point and document all
limitations. Specially regarding cross-schema dependencies, where it
might skip the dependencies for some objects (e.g. partitioned tables)
but works with others, e.g. functions in check constraints:

CREATE SCHEMA s1;
CREATE OR REPLACE FUNCTION s1.f(text)
RETURNS boolean LANGUAGE sql AS
$$ SELECT $1 <> ''; $$;

CREATE SCHEMA s2;
CREATE TABLE s2.t (id int, name text CHECK (s1.f(name)));

CREATE SCHEMA s3 LIKE s2 INCLUDING ALL;

\d s2.t
Table "s2.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
Check constraints:
"t_name_check" CHECK (s1.f(name))

\d s3.t
Table "s3.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
Check constraints:
"t_name_check" CHECK (s1.f(name))

I'll take a look at the code later today or tomorrow.

Thanks!

Best, Jim