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

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