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

Re: Flat File unload/export/copy

From: "Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov>
To: <steve(at)retsol(dot)co(dot)uk>
Cc: "PostGreSQL" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Flat File unload/export/copy
Date: 2008-09-12 17:16:21
Message-ID: 53F9CF533E1AA14EA1F8C5C08ABC08D20197A1FD@ZDND.DND.boston.cob (view raw or flat)
Thread:
Lists: pgsql-novice
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 	

In response to

Responses

pgsql-novice by date

Next:From: Steve TDate: 2008-09-12 17:17:13
Subject: Re: Flat File unload/export/copy
Previous:From: Steve TDate: 2008-09-12 17:14:42
Subject: Re: Flat File unload/export/copy

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