Re: loading and unloading rows - Mailing list pgsql-admin

As long as you are going to use pipes to do your "load/unload" or "read from
one cluster, copy to another", why not go with:

/usr/local/pgsql/bin/psql -U xxxx -d xxxx -h xxxx \
    -P format=unaligned -P fieldsep='\t' -P tuples_only \
    -c "select * from tab where yoo='hoo'" | /usr/local/pgsql/bin/psql
-U xxxx -d xxxx -h xxxx \
    -c "copy bar from stdin"

You don't have to do the text processing Bhuvan described, just obviate it
with the right options to psql. You don't need additional functionality in
postgresql unless you want to do it all with SQL rather than from a shell or
other client interface (you may have problems using named pipes from, say, a
python interface running on Windows, but you can work around that too).

Finally, you're not asking for a where clause for copying from a text file
to a pg table, are you? THAT is going to take a temporary table in any case,
it seems, so why not do it yourself?

Cheers,
    Murthy

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Saturday, November 02, 2002 20:24
To: Naomi Walker
Cc: Bhuvan A; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] loading and unloading rows



We do have /contrib/dblink in 7.3 that allows cross-db query access.  We
don't have any pass-data-between-db's capability that I know of.

---------------------------------------------------------------------------

Naomi Walker wrote:
> Exactly.  My Informix background is showing.  Is there a way to officially

> get this put on a to do list?  I'd be most appreciative of the value this
adds.
>
> Slightly off subject, is there a way to read from one cluster and insert
> into a database in another cluster (local or remote)?  I think I read this

> is coming in the future.  In Informix-land, we used to do this through a
> named pipe.  It was quite handy.
>
> At 10:39 PM 11/1/02 -0500, Bruce Momjian wrote:
>
> >Yes, agreed, it would be nice to have WHERE as part of COPY.  In fact, I
> >like Informix's solution, which merges data in/out with INSERT/SELECT:
> >
> >         UNLOAD TO '/tmp/x'
> >         SELECT *
> >         FROM tab;
> >
> >and
> >
> >         LOAD FROM '/tmp/x'
> >         INSERT INTO tab;
> >
> >It is tough to beat this flexibility.
> >
>
>---------------------------------------------------------------------------
> >
> >Naomi Walker wrote:
> > >
> >
> > > The problem with COPY, I think, is that I cannot use a WHERE
> > > statement.  I'd like to just unload certain rows from a table.  If I
> > wanted
> > > the whole table, i'll do a pg_dump or copy.
> > >
> >
> > > Again, i'd like a way to easily unload some selected rows  (select *
table
> > > where foo=X) from a table, and save them, or load them in another
cluster,
> > > etc.  Short of writing them to a temp table, then pg_dumping, or some
> > > klunk-oid mething, I do not see a clean way.
> > >
> > > Back to the maddening crowd.  Anyone at least agreed this is
> > needed?  Could
> > > we add "where"  clauses to COPY?  That would be perfect.
> > >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)