Re: Add CREATE SCHEMA ... LIKE support

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