Re: Flat File unload/export/copy - Mailing list pgsql-novice

Thanks Jeff.
I'll play tomorrow and see what I can get. It looks like Regina is correct for 8.2+ and they have modified the copy to do this - but it doesn't appear to be in 8.1.

On Fri, 2008-09-12 at 13:03 -0400, Jeff Soules wrote:

As a workaround if you won't be doing this much, couldn't you do the
select into a temporary table, then do something like

copy temptable (recno,code,name) to '/tmp/my/file';

and then just drop the temporary table?

Obviously not elegant, but at least it saves you having to trim the
resulting csv.

On Fri, Sep 12, 2008 at 12:10 PM, Steve T <steve@retsol.co.uk> wrote:
> Regina,
> I wasn't aware that you could!
> I just tried and that syntax gave me an error:
> rel_reinsure=# copy (select * from client where name like '%BERT%') to
> '/tmp/stevet.clients';
> ERROR:  syntax error at or near "(" at character 6
> LINE 1: copy (select * from client where name like '%BERT%') to '/tm...
>
> - I can do though:
> rel_reinsure=# copy client (recno,code,name) to '/tmp/stevet.clients';
>
> ... or select all columns, but that is 'too global' and I just want 'WHERE
> name LIKE '%BERT%' type syntax.
>
>
> On Fri, 2008-09-12 at 11:31 -0400, Obe, Regina wrote:
>
> And why can't you use copy?
>
> Something like below -
>
> COPY (SELECT * FROM sometable WHERE somevalue LIKE '%') TO
> '/path/to/textfile.csv'
>
> WITH NULL As 'NULL' CSV HEADER QUOTE AS '"';
>
>
>
>
> ________________________________
>
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Steve T
> Sent: Friday, September 12, 2008 11:16 AM
> To: PostGreSQL
> Subject: [NOVICE] Flat File unload/export/copy
>
>
> Is there a command that copies data from a table to a flat file for
> specified selection criteria?
> IE akin to a copy but not dumping a whole file?
> If I'm about to do a major change to data in a table, I'd like to be
> able to keep a copy of the data before the change (I know I can
> Begin...rollback should things go wrong).
>
> Is there a command that I've missed? (Informix had an 'UNLOAD TO blah
> SELECT blah..' type command which presumably was proprietary).
> -----------------------------------------
> The substance of this message, including any attachments, may be
> confidential, legally privileged and/or exempt from disclosure
> pursuant to Massachusetts law. It is intended
> solely for the addressee. If you received this in error, please
> contact the sender and delete the material from any computer.
>
>
>
>
> Steve Tucknott
> ReTSol Ltd
>
> DDI:         01323 488548
> Mobile:     0773 671 5772

Steve Tucknott ReTSol Ltd DDI:         01323 488548 Mobile:     0773 671 5772