Clone schema in PostgreSQL

Hi everyone,

My goal is to keep the post short, so If you don't know what is schema access this link.

Schemas can be useful to clients, maybe a schema by the client or schema by "category". In my job(our business is based in soccer) we use schemas by championship and year.
A sample :
ucl_2018
ucl_2017
championship *that's the model

Ok, now we need to create a new schema ucl_2019, right?

I researched and after some time, I found a function to help me, but I needed to do changes. I didn't remember exactly what I needed to do, so I decided to share the function I've been using. Another thing this function will create everything indexes, keys, foreign keys, etc...

Ok, that is the function you need to run in your database.

After that, you only need to run

select clone_schema('source','destination')

In my case,

select clone_schema('championship','ucl_2019');

I hope it can be useful to you. I love PostgreSQL, so I want to write more about other features I like or I think that can be useful.
My new post will be a sequence for that, I'll show how to update the schemas, running migrations in the schemas. I wrote the post

Comments

[Jörg Thomsen]

great function, thanks

I had to add quote_ident( befor the sequence and table names becaus of a bad names in my db.

25: SELECT QUOTE_IDENT(sequence_name::text) FROM ...
32: SELECT QUOTE_IDENT(table_name::text) FROM ...

[Renato Suero]

thanks for your info, it will help others :)

[Miguel Mendoza Molina]

this is amazing, saves me a lot of time, thanks for sharing this information with everyone

[Renato Suero]

Hi Miguel, thanks for the feedback I'm so happy to know my post as helpful to someone

[Juan Castillo]

Which Postgresl version are you using ? I tried it on 11.5 but got Syntax errror.

[Renato Suero]

I've been used 10.6 can you tell me the error, maybe I can help you