Skip site navigation (1) Skip section navigation (2)

Re: Flat File unload/export/copy

From: Steve T <steve(at)retsol(dot)co(dot)uk>
To: "Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov>
Cc: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Flat File unload/export/copy
Date: 2008-09-12 17:18:53
Message-ID: 1221239933.3603.215.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-novice
Regina,
Thanks again - you're right, that syntax is there in 8.2+ but I'm on an
older 8.1 release.

Apologies to all for not having stated the version I was using earlier.


On Fri, 2008-09-12 at 13:16 -0400, Obe, Regina wrote:
> Steve,
> Seems to work okay for me.  
> Which version of PostgreSQL are you using?
>  
> Looks like the query option was introduced in 8.2
>  
> http://www.postgresql.org/docs/8.2/static/sql-copy.html
>  
>  
> I did a sample
>  
> copy (SELECT * FROM information_schema.tables WHERE table_schema LIKE
>  'pg%') to '/temp/pgcat.csv';
>  
> which should work in any db if you have admin rights and worked fine
> for me on an 8.2 install.
>  
> I suppose if you are using a version prior to 8.2, you could use the
> documented workaround
>  
> BEGIN;
> CREATE TEMP TABLE a_list_countries AS
>     SELECT * FROM country WHERE country_name LIKE 'A%';
> COPY a_list_countries TO '/usr1/proj/bray/sql/a_list_countries.copy';
> ROLLBACK;
> Hope that helps,
> Regina
>  
> 
> 
> ______________________________________________________________________
> From: Steve T [mailto:steve(at)retsol(dot)co(dot)uk]
> Sent: Fri 9/12/2008 12:10 PM
> To: Obe, Regina
> Cc: PostGreSQL
> Subject: Re: [NOVICE] Flat File unload/export/copy
> 
> 
> 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(at)postgresql(dot)org
> > [mailto:pgsql-novice-owner(at)postgresql(dot)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

In response to

pgsql-novice by date

Next:From: Len ShapiroDate: 2008-09-15 03:04:07
Subject: GRANT question
Previous:From: Steve TDate: 2008-09-12 17:17:13
Subject: Re: Flat File unload/export/copy

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group