Обсуждение: Copy data from one table to another
Hi All, Would someone be so kind as to remind me of the syntax required to copy data from one table to another? Kind Regards, Keith
On 2/24/06 1:42 PM, "Keith Worthington" <keithw@narrowpathinc.com> wrote: > Hi All, > > Would someone be so kind as to remind me of the syntax required to copy data > from one table to another? Hi, Keith. Will: Insert into table2 [column1, ...] Select * from table1; Do what you want? Sean
If you want an exact copy (using another method), without indexes or relying objects, you shall try:
`SELECT * INTO table_b FROM table_a`
Check the manpages, SELECT, or in the psql shell: \h SELECT
--------------
Another option:
pg_dump the data as INSERT replacing the table name on the result with the new table name, then psql'it.
----------
Another option:
run a classic pg_dump, using COPY, and replace the table name with the new one.
-------
Consider in all cases, that the index maintenance will be a cost if you already have the indexes created before running the populate. You may want to drop that indexes, populate and recreate indexes after.
Vacuum analyze should be the last command on this move, afaik.
Best wishes,
Guido
--
/"\ ASCII Ribbon Campaign .
\ / - NO HTML/RTF in e-mail .
X - NO Word docs in e-mail .
/ \ -----------------------------------------------------------------
`SELECT * INTO table_b FROM table_a`
Check the manpages, SELECT, or in the psql shell: \h SELECT
--------------
Another option:
pg_dump the data as INSERT replacing the table name on the result with the new table name, then psql'it.
----------
Another option:
run a classic pg_dump, using COPY, and replace the table name with the new one.
-------
Consider in all cases, that the index maintenance will be a cost if you already have the indexes created before running the populate. You may want to drop that indexes, populate and recreate indexes after.
Vacuum analyze should be the last command on this move, afaik.
Best wishes,
Guido
On 2/24/06, Sean Davis < sdavis2@mail.nih.gov> wrote:
On 2/24/06 1:42 PM, "Keith Worthington" < keithw@narrowpathinc.com> wrote:
> Hi All,
>
> Would someone be so kind as to remind me of the syntax required to copy data
> from one table to another?
Hi, Keith. Will:
Insert into table2 [column1, ...]
Select * from table1;
Do what you want?
Sean
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
/"\ ASCII Ribbon Campaign .
\ / - NO HTML/RTF in e-mail .
X - NO Word docs in e-mail .
/ \ -----------------------------------------------------------------
> On Fri, 24 Feb 2006 19:19:58 +0000, Guido Barosio wrote > > On 2/24/06, Sean Davis <sdavis2@mail.nih.gov> wrote: > > > On 2/24/06 1:42 PM, "Keith Worthington" <keithw@narrowpathinc.com> wrote: > > > > > > Hi All, > > > > > > Would someone be so kind as to remind me of the syntax > > > required to copy data from one table to another? > > > > Hi, Keith. Will: > > > > Insert into table2 [column1, ...] > > Select * from table1; > > > > Do what you want? > > > > Sean > > If you want an exact copy (using another method), without indexes or relying > objects, you shall try: > > `SELECT * INTO table_b FROM table_a` > > Check the manpages, SELECT, or in the psql shell: \h SELECT > -------------- > > Another option: > > pg_dump the data as INSERT replacing the table name on the result with > the new table name, then psql'it. > > ---------- > > Another option: > > run a classic pg_dump, using COPY, and replace the table name with the > new one. > > ------- > > Consider in all cases, that the index maintenance will be a cost if you > already have the indexes created before running the populate. You may want > to drop that indexes, populate and recreate indexes after. > > Vacuum analyze should be the last command on this move, afaik. > > Best wishes, > Guido Thank you both Sean and Guido. One problem I was having was that I didn't want to use a SELECT INTO to create a temporary table. Then have to use the COPY command to write the data to a file and finally a second COPY command to read the data into the target table. The other challenge was that I couldn't just use the SELECT INTO command because the target table already existed. I ended up with an insert and a subselect. I do not know if this is the best way but it worked for me. INSERT INTO tbl_target ( SELECT column_a, column_b, column_c FROM tbl_source WHERE condition ); Kind Regards, Keith