Thread: copy data from one db into another via copy & psql

Can I copy from one db (via COPY) and pipe the results to a psql/COPY stmt so
I can load the data into a table in the second db 'inline' without writing to
& reading from a flat file?

In response to Kevin Kempter :
> Can I copy from one db (via COPY) and pipe the results to a psql/COPY stmt so
> I can load the data into a table in the second db 'inline' without writing to
> & reading from a flat file?

Yes, but keep in mind, COPY cant create the table on the destination. If
the destination contains a (empty) table it's no problem.

If the destination-db doesn't contain the table you can also use
pg_dump:

kretschmer@tux:~$ createdb new_test
kretschmer@tux:~$ pg_dump -t foo test | psql -X new_test -f -
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
kretschmer@tux:~$ psql new_test -c "select * from foo"
      t
--------------
 a short test
(1 row)


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

On Thu, May 20, 2010 at 10:59 PM, Kevin Kempter
<kevink@consistentstate.com> wrote:
> Can I copy from one db (via COPY) and pipe the results to a psql/COPY stmt so
> I can load the data into a table in the second db 'inline' without writing to
> & reading from a flat file?

That's pretty much what

pg_dump -t tablename -d dbname | psql dbname

does.  Add a -a and it's data only:

pg_dump -a -t tablename -d dbname | psql dbname

On 2010-05-21, Kevin Kempter <kevink@consistentstate.com> wrote:
> Can I copy from one db (via COPY) and pipe the results to a psql/COPY stmt so
> I can load the data into a table in the second db 'inline' without writing to
> & reading from a flat file?

Yes.

  COPY ... TO stdout;

at the source and

  COPY ... FROM stdin;

at the destination.



I use this with the

  COPY ( query ) TO  STDOUT ;

syntax when I want to copy only a few rows.