5

Is there any way to create a new schema from template a similar way to creating new database?

CREATE DATABASE music TEMPLATE template0;

Maybe something like this imaginary snippet

CREATE SCHEMA myschema TEMPLATE public;

I don't want to use pg_dump or pg_restore the only available option to me is SQL commands.

PS. I use PostgreSQL 13.1

5
  • 1
    What properties from the template schema would you like to copy? Commented Feb 10, 2021 at 6:02
  • 1
    There is no such feature built-in, you will have to create your own templating system based on SQL scripts. The usual approach for repeatable scripts is to store them in a version control system (Git, Subversion) and use schema migration tools like Liquibase and Flyway to manage the creation and rollout
    – user1822
    Commented Feb 10, 2021 at 7:23
  • @Lennart I only need to copy tables structure from public schema nothing else.
    – mbnoimi
    Commented Feb 10, 2021 at 9:26
  • @mbnoimi, I'm not aware of anything like that. The template would have to be specific for each and every product and would need to be updated every time something is changed in a product. I agree with a-horse-with-no-name, put your schema(s) under source control, and have a migration utility that can transform your schema from one state to another. Commented Feb 10, 2021 at 10:38
  • The main reason of my question is for applying multi-tenancy using schema. So Liquibase or Flyway are not suitable solutions for me.
    – mbnoimi
    Commented Feb 10, 2021 at 16:30

1 Answer 1

4
-- Create a new schema
CREATE SCHEMA myschema;

-- Copy tables from the template schema to the new schema
DO $$DECLARE
    tbl_record RECORD;
BEGIN
    FOR tbl_record IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'CREATE TABLE myschema.' || tbl_record.tablename || ' (LIKE public.' || tbl_record.tablename || ' INCLUDING CONSTRAINTS)';
    END LOOP;
END$$;

-- Copy data from the template schema's tables to the new schema's tables
DO $$DECLARE
    tbl_record RECORD;
BEGIN
    FOR tbl_record IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'INSERT INTO myschema.' || tbl_record.tablename || ' SELECT * FROM public.' || tbl_record.tablename;
    END LOOP;
END$$;

Creates a new schema called "myschema". Copies the tables from the "public" schema to the "myschema" schema using the CREATE TABLE ... LIKE statement. Copies the data from the tables in the "public" schema to the corresponding tables in the "myschema" schema using the INSERT INTO ... SELECT statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.