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

Re: Restoring a table with a different name

From: elein <elein(at)varlena(dot)com>
To: Adam Ruth <aruth(at)intercation(dot)com>
Cc: Mike Nolan <nolan(at)gw(dot)tssi(dot)com>, jimw(at)kelcomaine(dot)com,pgsql general list <pgsql-general(at)postgresql(dot)org>
Subject: Re: Restoring a table with a different name
Date: 2004-02-28 03:33:33
Message-ID: 20040227193333.J7999@cookie.varlena.com (view raw or flat)
Thread:
Lists: pgsql-general
Alternatively, you can do the renaming on the db
and then dump the renamed table.  But you'd need 
enough room and time for it.  (Not a good solution for
very large tables...) A four line shell
script might do it.  Validate and add correct syntax
to taste.

psql olddb ... -c "create table deleteme as select * from account;"
pg_dump olddb ... --table=deleteme > delme.sql
psql newdb < delme.sql
psql olddb ... -c "drop table deleteme;"
rm delme.sql ; # OK, 5 lines

elein
============================================================
elein(at)varlena(dot)com        Varlena, LLC        www.varlena.com
                         1-866-VARLENA
          PostgreSQL Consulting, Support & Training   

PostgreSQL General Bits   http://www.varlena.com/GeneralBits/
=============================================================
I have always depended on the [QA] of strangers.




On Fri, Feb 27, 2004 at 07:06:58PM -0700, Adam Ruth wrote:
> You may be better off with renaming the existing table, importing, then 
> doing more renaming to get everything in the right place:
> 
> alter table memmast rename to memmast_temp;
> 
> <import table>
> 
> alter table memmast rename to wk_memmast;
> 
> alter table memmast_temp rename to memmast;
> 
> That way you don't need to muck with the dump file.
> 
> On Feb 27, 2004, at 7:03 PM, Mike Nolan wrote:
> 
> >>>If I edit the dump file with 'sed' to change the table name, I get
> >>>'invalid command \N' errors trying to reload it.
> >>
> >>What sed syntax are you using?
> >
> >Here's the command line I used:
> >
> >   sed -e 's/memmast/wk_memmast/' memmast.dmp > wk_memmast.dmp
> >
> >I see two potential problems here, and it took both of them to bite me.
> >
> >One is that I'm not changing all occurrences of 'memmast' to 
> >'wk_memmast'.
> >The other is that the string 'memmast' can and does occur within the
> >name of another column, so the name of that column was edited by sed
> >in the CREATE TABLE statement but not in the LOAD command.
> >
> >Changing the command line to:
> >
> >   sed -e 's/ memmast / wk_memmast /' memmast.dmp > wk_memmast.dmp
> >
> >works, and without changing that column name.
> >
> >I think, however, that I may need to go with the other method (copying
> >the table and dumping/restoring the copy), because the restore runs 
> >into
> >name conflicts with several indexes and there is a trigger procedure
> >on that table.
> >--
> >Mike Nolan
> >
> >---------------------------(end of 
> >broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> >               http://www.postgresql.org/docs/faqs/FAQ.html
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2004-02-28 04:06:52
Subject: Re: setting datestyle for different databases within a cluster
Previous:From: dewins murilloDate: 2004-02-28 03:24:26
Subject: Postgresql bajo Windows Míralo Ve

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