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(at)eldocomp(dot)com
> 602-604-3100 x242
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

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