Re: Restoring a table with a different name

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