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
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(at)varlena(dot)com Varlena, LLC www.varlena.com
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
> >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
> >name conflicts with several indexes and there is a trigger procedure
> >on that table.
> >Mike Nolan
> >---------------------------(end of
> >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 Lane||Date: 2004-02-28 04:06:52|
|Subject: Re: setting datestyle for different databases within a cluster |
|Previous:||From: dewins murillo||Date: 2004-02-28 03:24:26|
|Subject: Postgresql bajo Windows Míralo Ve|