Skip site navigation (1) Skip section navigation (2)

Re: How to copy a schema under another name in same database

From: "Daniel Gour" <Daniel(dot)Gour(at)adacel(dot)com>
To: "Bastiaan Olij" <lists(at)basenlily(dot)nl>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: How to copy a schema under another name in same database
Date: 2008-07-30 13:10:11
Message-ID: 6B0B2B2B3D1F634F99AD4D99BA30A20B02FEE937@adacel-mail.adacelcanada.com (view raw or flat)
Thread:
Lists: pgsql-novice
Thank you for your input, but I was looking for was a solution to copy all of
the database objects (table, indexes, functions, etc) (and their data) that
are under one schema and copy them into another schema.

For your info, your suggested command works well under PostgreSQL as well.

Thanks again!

---------------------------------
Daniel Gour, Eng.
SPT/IOS/DB CSCI Lead

-----Original Message-----
From: Bastiaan Olij [mailto:lists(at)basenlily(dot)nl] 
Sent: Tuesday, July 29, 2008 7:06 PM
To: Daniel Gour
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] How to copy a schema under another name in same
database

Hi Daniel,

Actually not 100% sure if this works the same for Postgres but I always 
did the following in Sybase:

select * into newtabel from oldtable where 1=2

Creates a duplicate of oldtable but without the data (remove the where 
if you want the data to be copied aswell). You still need to recreate 
the indexes if those are needed off course.

-- 
Kindest Regards,

Bastiaan Olij
e-mail/MSN: bastiaan(at)basenlily(dot)nl
web: http://www.basenlily.nl
Skype: Mux213
http://www.linkedin.com/in/bastiaanolij



Daniel Gour wrote:
>
> Hello! I have a PostgreSQL (8.3.3, WinXP + linux) database with 
> multiple schemas. I would like, on a regular basis, to be able to copy 
> the structure and data of one schema under a new schema, using of 
> course a different name. What would be the easiest way?
>
> - I have seen the TODO entry "Add CREATE SCHEMA ... LIKE that copies a 
> schema", so I guess an easy solution (a single pgsql command) is not 
> yet available...
>
> - I guess the alternative is to use pg_dump to dump a single schema 
> and use pg_restore, but how can I restore the dumped information under 
> another schema? I would like to avoid dumping it as an sql script and 
> having it to modify it manually, because this will be a regular 
> operation that I would like to automate in my application.
>
> Thanks in advance for any insight you can provide!
>
> ---------------------------------
> Daniel Gour
> Adacel Inc.
>



pgsql-novice by date

Next:From: Daniel GourDate: 2008-07-30 13:11:35
Subject: Re: How to copy a schema under another name in same database
Previous:From: Obe, ReginaDate: 2008-07-30 11:14:10
Subject: Re: How to copy a schema under another name in same database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group