Re: Copy data from one table to another

> On Fri, 24 Feb 2006 19:19:58 +0000, Guido Barosio wrote
> > On 2/24/06, Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> wrote:
> > > On 2/24/06 1:42 PM, "Keith Worthington" <keithw(at)narrowpathinc(dot)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