snippetsqlMinor
How to create SCHEMA from a template?
Viewed 0 times
templatecreatehowfromschema
Problem
Is there any way to create a new schema from template a similar way to creating new database?
Maybe something like this imaginary snippet
I don't want to use
PS. I use PostgreSQL 13.1
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.