HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to create SCHEMA from a template?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
templatecreatehowfromschema

Problem

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

Solution

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

Code Snippets

-- 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$$;

Context

StackExchange Database Administrators Q#285082, answer score: 3

Revisions (0)

No revisions yet.