Обсуждение: restore
Hi All, Is there an easy way to restore to a new table where the column name have been changed but data remains the same? For example I am trying to restore from existing system, table1(col1) to table1(col2) and it is erroring out on the new column name even though it is a data only restore. I was just wondering if there is a quick way to bypass this. Let me know. Thanks. Dinesh
Dinesh Bhandary <dbhandary@iii.com> wrote: > Is there an easy way to restore to a new table where the column > name have been changed but data remains the same? Restore to the old name and then rename the column? Access the backup file and change the COPY statement to the new column name? If you can redo the dump, you don't mind the load running a little slower, and the column order is the same -- dump with the --inserts switch? -Kevin
There are a couple of ways. Assuming that it's the same database, and it's up and running, you could do this:
Assuming:
table foo (col1 text, col2 int);
table bar (col2 text, col3 int);
insert into bar (select * from foo);
would stick everything from foo.col1 and foo.col2 into bar.col2 and bar.col3 respectively.
Using pg_dump, you could dump just the table:
pg_dump -t foo mydatabase
and edit the CREATE TABLE statement.
Assuming:
table foo (col1 text, col2 int);
table bar (col2 text, col3 int);
insert into bar (select * from foo);
would stick everything from foo.col1 and foo.col2 into bar.col2 and bar.col3 respectively.
Using pg_dump, you could dump just the table:
pg_dump -t foo mydatabase
and edit the CREATE TABLE statement.
Hi All,
Is there an easy way to restore to a new table where the column name
have been changed but data remains the same?
For example I am trying to restore from existing system, table1(col1)
to table1(col2) and it is erroring out on the new column name even
though it is a data only restore. I was just wondering if there is a
quick way to bypass this. Let me know.
Thanks.
Dinesh
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Awesome, this will work. It won't retain the column name in insert into statement. I was not sure if this will work with postgres 8.2.5 but it does. Thanks. Dinesh On 10/5/2010 11:15 AM, Kevin Grittner wrote: > Dinesh Bhandary<dbhandary@iii.com> wrote: > >> Is there an easy way to restore to a new table where the column >> name have been changed but data remains the same? > > Restore to the old name and then rename the column? > > Access the backup file and change the COPY statement to the new > column name? > > If you can redo the dump, you don't mind the load running a little > slower, and the column order is the same -- dump with the --inserts > switch? > > -Kevin > > >
On 10/5/10 11:08 AM, Dinesh Bhandary wrote: > Hi All, > > Is there an easy way to restore to a new table where the column name have been changed but data remains the same? > For example I am trying to restore from existing system, table1(col1) to table1(col2) and it is erroring out on the newcolumn name even though it is a data only restore. I was just wondering if there is a quick way to bypass this. Let meknow. If your data file is plain text with a COPY or INSERT commands, do this using psql: begin; alter table1 rename col2 to col1; \i datafile.sql alter table1 rename col1 to col2; commit; Craig