Thread: COPY to STDOUT and pipes

Hi List;


I want to run a copy (based on a select) to STDOUT and pipe it to a
psql copy from STDIN on a different host.

here's what I have:

1) a .sql file that looks like this:

copy (
select
   cust_id,
   cust_name,
   last_update_dt
from sl_cust
)
to STDOUT
with delimiter '|'


This works.

However I want to pipe the resulting data into a psql statement that
does a copy IN to a table on another host. I can't seem to get it
right. I tried this:

psql -f file1.sql | psql -h newhost -f file2.sql

where file1.sql is the copy statement above and file2.sql does a copy
table from STDIN with delimiter '|'

Any thoughts on what I'm doing wrong?

Thanks in advance...

/Kevin

kevin kempter wrote:

> Any thoughts on what I'm doing wrong?

I suspect that pg_dump is going to do a better job than using psql to
generate the input for the remote load. pg_dump can dump single tables
and can use COPY style data formatting.

As for why your current command isn't working ... You omitted the SQL in
`file2.sql' that you use for the restore, which makes things harder. At
a guess I'd say the "stdin" the input copy is seeing is expected to be
the text directly following the COPY ... FROM command in the input file,
rather than the psql command's stdin.

I do have one suggestion that's ugly but may work if you can't figure
out what's going wrong with the existing method and you're not happy
with using pg_dump for some reason:

You could potentially insert psql \echo commands into the first psql
command, so the command you're using to extract the data produces a
valid sequence of SQL commands that the second psql can read from stdin
(instead of using -f to read a command file). So if `file1.sql' becomes:

\echo 'COPY tablename FROM STDIN;'
copy (
select
  cust_id,
  cust_name,
  last_update_dt
from sl_cust
)
to STDOUT
with delimiter '|'
\echo '\\\.'

then you might be able to use a command line like:

psql -f file1.sql | psql -h newhost

(note that the second psql is reading the first one's stdout as its stdin).

--
Craig Ringer

kevin kempter wrote:
> Hi List;
>
>
> I want to run a copy (based on a select) to STDOUT and pipe it to a
> psql copy from STDIN on a different host.
>
> here's what I have:
>
> 1) a .sql file that looks like this:
>
> copy (
> select
>   cust_id,
>   cust_name,
>   last_update_dt
> from sl_cust
> )
> to STDOUT
> with delimiter '|'
>
>
> This works.
>
> However I want to pipe the resulting data into a psql statement that
> does a copy IN to a table on another host. I can't seem to get it
> right. I tried this:
>
> psql -f file1.sql | psql -h newhost -f file2.sql
>
> where file1.sql is the copy statement above and file2.sql does a copy
> table from STDIN with delimiter '|'
>
> Any thoughts on what I'm doing wrong?
no database unless your logged in as someone with the same name as the
databases on both servers?

C:\>psql -d gpdms_nunit -c "copy (select * from pg_class) to stdout with
delimiter '|'" | psql -d gpdms -c "create table foo (like pg_class);
copy foo from stdin with delimiter '|';"

works for me on 8.3.0 win32

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au