Обсуждение: How to copy a schema under another name in same database
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.
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@basenlily.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. >
Never had a need for this. One thought that comes to mind write a plpgsql function that takes in name of new schema and old schema and does something like below --Create new tables in new schema FOR tbltocopy IN(SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = param_source_schema ORDER BY table_name) EXECUTE('CREATE TABLE ' || param_dest_schema || '.' || tbltocopy || '( LIKE ' || param_source_schema || '.' || tbltocopy || ' INCLUDING DEFAULT CONSTRAINTS INDEXES '); INSERT INTO ' || param_dest_schema || '.' || tbltocopy || ' SELECT * FROM ' param_source_schema || '.' || tbltocopy || ';'); NEXT; Hope that helps, Regina -----Original Message----- From: pgsql-novice-owner@postgresql.org on behalf of Daniel Gour Sent: Tue 7/29/2008 9:20 AM To: pgsql-novice@postgresql.org Subject: [NOVICE] How to copy a schema under another name in same database 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. ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
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@basenlily.nl] Sent: Tuesday, July 29, 2008 7:06 PM To: Daniel Gour Cc: pgsql-novice@postgresql.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@basenlily.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. >
Thanks for the information! That looks promising, I will attempt to implement it this week and let you know. --------------------------------- Daniel Gour Adacel Inc. -----Original Message----- From: Obe, Regina [mailto:robe.dnd@cityofboston.gov] Sent: Wednesday, July 30, 2008 7:14 AM To: Daniel Gour; pgsql-novice@postgresql.org Subject: RE: [NOVICE] How to copy a schema under another name in same database Never had a need for this. One thought that comes to mind write a plpgsql function that takes in name of new schema and old schema and does something like below --Create new tables in new schema FOR tbltocopy IN(SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = param_source_schema ORDER BY table_name) EXECUTE('CREATE TABLE ' || param_dest_schema || '.' || tbltocopy || '( LIKE ' || param_source_schema || '.' || tbltocopy || ' INCLUDING DEFAULT CONSTRAINTS INDEXES '); INSERT INTO ' || param_dest_schema || '.' || tbltocopy || ' SELECT * FROM ' param_source_schema || '.' || tbltocopy || ';'); NEXT; Hope that helps, Regina -----Original Message----- From: pgsql-novice-owner@postgresql.org on behalf of Daniel Gour Sent: Tue 7/29/2008 9:20 AM To: pgsql-novice@postgresql.org Subject: [NOVICE] How to copy a schema under another name in same database 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. ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
Daniel, I had a typo in the below that I just noticed. Should be I think - but I'm sure I screwed up in other ways, but hopefully you get the idea. FOR tbltocopy IN(SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = param_source_schema ORDER BY table_name) EXECUTE('CREATE TABLE ' || param_dest_schema || '.' || tbltocopy || '( LIKE ' || param_source_schema || '.' || tbltocopy || ' INCLUDING DEFAULT CONSTRAINTS INDEXES ); INSERT INTO ' || param_dest_schema || '.' || tbltocopy || ' SELECT * FROM ' param_source_schema || '.' || tbltocopy || ';'); If you need to copy views as well, that's a bit trickier since I assume you would want your views to reference the specific schema objects within the destination schema not the source schema. For that take a look at information_schema.views -- it has a field called view_definition. You could use that to build the view and then macro replace the references to the schema with the new schema I guess. Something like DECLARE vwdef information_schema.views --this part is iffy not sure if a type is created for information_schema stuff FOR vwdef IN(SELECT * FROM information_schema.views WHERE table_schema = param_source_schema ORDER BY table_name) EXECUTE('CREATE VIEW ' || param_dest_schema || '.' || vwdef.table_name || '( AS ' || REPLACE(vwdef.view_definition, param_source_schema || '.', param_dest_schema || '.') || ';'); Hope that helps, Regina -----Original Message----- From: Daniel Gour [mailto:Daniel.Gour@adacel.com] Sent: Wednesday, July 30, 2008 9:12 AM To: Obe, Regina Cc: pgsql-novice@postgresql.org Subject: RE: [NOVICE] How to copy a schema under another name in same database Thanks for the information! That looks promising, I will attempt to implement it this week and let you know. --------------------------------- Daniel Gour Adacel Inc. -----Original Message----- From: Obe, Regina [mailto:robe.dnd@cityofboston.gov] Sent: Wednesday, July 30, 2008 7:14 AM To: Daniel Gour; pgsql-novice@postgresql.org Subject: RE: [NOVICE] How to copy a schema under another name in same database Never had a need for this. One thought that comes to mind write a plpgsql function that takes in name of new schema and old schema and does something like below --Create new tables in new schema FOR tbltocopy IN(SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = param_source_schema ORDER BY table_name) EXECUTE('CREATE TABLE ' || param_dest_schema || '.' || tbltocopy || '( LIKE ' || param_source_schema || '.' || tbltocopy || ' INCLUDING DEFAULT CONSTRAINTS INDEXES '); INSERT INTO ' || param_dest_schema || '.' || tbltocopy || ' SELECT * FROM ' param_source_schema || '.' || tbltocopy || ';'); NEXT; Hope that helps, Regina -----Original Message----- From: pgsql-novice-owner@postgresql.org on behalf of Daniel Gour Sent: Tue 7/29/2008 9:20 AM To: pgsql-novice@postgresql.org Subject: [NOVICE] How to copy a schema under another name in same database 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. ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
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? Backup the schema with pg_dump; edit file; change schema name and restore? If schema names do not show up in data; then this might work: pg_dump ... | sed s:schema1:schema2:g | psql
Thanks for the suggestion, but I need the application to be multi-platform (I'm using Qt). Since sed is not natively available on XP... Also, as you mention, it is risky to blindly replace one string for another in the whole sql script. The solution is however simple and elegant, maybe someone else will find it useful. Thanks! --------------------------------- Daniel Gour Adacel Inc. -----Original Message----- From: Frank Bax [mailto:fbax@sympatico.ca] Sent: Wednesday, July 30, 2008 12:51 PM To: PostgreSQL List - Novice Subject: Re: [NOVICE] How to copy a schema under another name in same database 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? Backup the schema with pg_dump; edit file; change schema name and restore? If schema names do not show up in data; then this might work: pg_dump ... | sed s:schema1:schema2:g | psql -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
On Wed, Jul 30, 2008 at 12:04 PM, Daniel Gour <Daniel.Gour@adacel.com> wrote: > Thanks for the suggestion, but I need the application to be multi-platform > (I'm using Qt). Since sed is not natively available on XP... Check out UnxUtils, it includes sed and friends: http://sourceforge.net/project/showfiles.php?group_id=9328 Good luck.
From: pgsql-novice-owner@postgresql.org on behalf of Daniel Gour
Sent: Wed 7/30/2008 1:04 PM
To: Frank Bax
Cc: PostgreSQL List - Novice
Subject: Re: [NOVICE] How to copy a schema under another name in same database
Thanks for the suggestion, but I need the application to be multi-platform
(I'm using Qt). Since sed is not natively available on XP... Also, as you
mention, it is risky to blindly replace one string for another in the whole
sql script.
The solution is however simple and elegant, maybe someone else will find it
useful. Thanks!
---------------------------------
Daniel Gour
Adacel Inc.
-----Original Message-----
From: Frank Bax [mailto:fbax@sympatico.ca]
Sent: Wednesday, July 30, 2008 12:51 PM
To: PostgreSQL List - Novice
Subject: Re: [NOVICE] How to copy a schema under another name in same
database
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?
Backup the schema with pg_dump; edit file; change schema name and
restore? If schema names do not show up in data; then this might work:
pg_dump ... | sed s:schema1:schema2:g | psql
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
"Rob Richardson" <Rob.Richardson@rad-con.com> writes: > In the help file under date and time functions, I see that intervals can be specified as "interval '3 hours' ". In a PgAdminSQL window, I can enter "select interval '3 hours' ", and it will return me "03:00:00", as expected. I can also enter"select '3 hours'::interval", and get the same result. Yet neither syntax works inside a function. > declare > ThreeHours interval; > begin > ThreeHours = interval '3 hours'; -- throws a syntax error > ThreeHours = '3 hours'::interval; -- also throws a syntax error > end; Either of those should work. I think your problem is that you're not quoting the whole function body correctly. Remember that the function body is itself a string constant. So if you were to try to write this as a single-quoted string, you'd need to double those embedded quotes: CREATE FUNCTION ... AS ' declare ThreeHours interval; begin ThreeHours = interval ''3 hours''; -- throws a syntax error ThreeHours = ''3 hours''::interval; -- also throws a syntax error end; ' LANGUAGE plpgsql; In any reasonably modern version of PG, there's a string constant syntax called "dollar quoting", which was invented specifically to make this less painful: CREATE FUNCTION ... AS $$ declare ThreeHours interval; begin ThreeHours = interval '3 hours'; -- throws a syntax error ThreeHours = '3 hours'::interval; -- also throws a syntax error end; $$ LANGUAGE plpgsql; If you need to use '$$' inside the function body, you could instead use $func$ or something like that as the outer quoting boundaries. (BTW, I would think that PgAdmin could handle these quoting details for you, but I'm really not very familar with it. Are you editing the function in a window that's specifically for function editing? If you're just typing the CREATE FUNCTION command as-is in a command window, then you'll have to deal with the nested-quoting issues for yourself.) regards, tom lane
Just to close the loop on my initial request, in the end we decided to go with the approach of renaming the schema under the new name, dump it, rename the schema back to its original name and do a restore of the dump, which will recreate the schema under the new name. Thanks to all for your suggestions, I will definitely keep the proposed solution below in my notes! --------------------------------- Daniel Gour Adacel inc. -----Original Message----- From: Obe, Regina [mailto:robe.dnd@cityofboston.gov] Sent: Wednesday, July 30, 2008 9:25 AM To: Daniel Gour Cc: pgsql-novice@postgresql.org Subject: RE: [NOVICE] How to copy a schema under another name in same database Daniel, I had a typo in the below that I just noticed. Should be I think - but I'm sure I screwed up in other ways, but hopefully you get the idea. FOR tbltocopy IN(SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = param_source_schema ORDER BY table_name) EXECUTE('CREATE TABLE ' || param_dest_schema || '.' || tbltocopy || '( LIKE ' || param_source_schema || '.' || tbltocopy || ' INCLUDING DEFAULT CONSTRAINTS INDEXES ); INSERT INTO ' || param_dest_schema || '.' || tbltocopy || ' SELECT * FROM ' param_source_schema || '.' || tbltocopy || ';'); If you need to copy views as well, that's a bit trickier since I assume you would want your views to reference the specific schema objects within the destination schema not the source schema. For that take a look at information_schema.views -- it has a field called view_definition. You could use that to build the view and then macro replace the references to the schema with the new schema I guess. Something like DECLARE vwdef information_schema.views --this part is iffy not sure if a type is created for information_schema stuff FOR vwdef IN(SELECT * FROM information_schema.views WHERE table_schema = param_source_schema ORDER BY table_name) EXECUTE('CREATE VIEW ' || param_dest_schema || '.' || vwdef.table_name || '( AS ' || REPLACE(vwdef.view_definition, param_source_schema || '.', param_dest_schema || '.') || ';'); Hope that helps, Regina -----Original Message----- From: Daniel Gour [mailto:Daniel.Gour@adacel.com] Sent: Wednesday, July 30, 2008 9:12 AM To: Obe, Regina Cc: pgsql-novice@postgresql.org Subject: RE: [NOVICE] How to copy a schema under another name in same database Thanks for the information! That looks promising, I will attempt to implement it this week and let you know. --------------------------------- Daniel Gour Adacel Inc. -----Original Message----- From: Obe, Regina [mailto:robe.dnd@cityofboston.gov] Sent: Wednesday, July 30, 2008 7:14 AM To: Daniel Gour; pgsql-novice@postgresql.org Subject: RE: [NOVICE] How to copy a schema under another name in same database Never had a need for this. One thought that comes to mind write a plpgsql function that takes in name of new schema and old schema and does something like below --Create new tables in new schema FOR tbltocopy IN(SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = param_source_schema ORDER BY table_name) EXECUTE('CREATE TABLE ' || param_dest_schema || '.' || tbltocopy || '( LIKE ' || param_source_schema || '.' || tbltocopy || ' INCLUDING DEFAULT CONSTRAINTS INDEXES '); INSERT INTO ' || param_dest_schema || '.' || tbltocopy || ' SELECT * FROM ' param_source_schema || '.' || tbltocopy || ';'); NEXT; Hope that helps, Regina -----Original Message----- From: pgsql-novice-owner@postgresql.org on behalf of Daniel Gour Sent: Tue 7/29/2008 9:20 AM To: pgsql-novice@postgresql.org Subject: [NOVICE] How to copy a schema under another name in same database 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. ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.