Re: loading and unloading rows

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:
>
> >
> >It is possible. Delimiters can be used while restoring the data file.
> >Null string specification can also be specified. See,
> >test_db=> \h COPY
> >for more details.
>
> 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.
>
>
> >In addition you should also do some find and replace in the data file
> >before restoring it to another database. They are,
> >s/\s*|\s*//g
> >s/^\s*//g
>
> Yes, sed is a fine tool, but this seems like such a fundamental need, i'm
> surprised there is not better method.
>
> 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.
>
> ----------------------------------------------------------------------------
> ----------------------------------
> Naomi Walker
> Eldorado Computing, Inc
> Chief Information Officer
> nwalker@eldocomp.com
> 602-604-3100 x242
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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
>

--
  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