Thread: 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).
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).
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.
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:
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 T wrote:
> 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...*
What version do you have? These three pages appear to have some
differences between 8.1 and 8.2:
http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
http://www.postgresql.org/docs/8.2/interactive/sql-copy.html
http://www.postgresql.org/docs/8.3/interactive/sql-copy.html
Sean,
8.1.10 - I read the docs first (which is a first for me!) before I posted and couldn't see a 'specific' data based row unload. I suppose I can always simply execute the sql and re-direct the output to a file (that just crossed my mind) - I'll try that tomorrow (I think I can unset headings in psql as well - so that will give me a pseudo 'unload/copy' file I think)
On Fri, 2008-09-12 at 12:20 -0400, Sean Davis wrote:
8.1.10 - I read the docs first (which is a first for me!) before I posted and couldn't see a 'specific' data based row unload. I suppose I can always simply execute the sql and re-direct the output to a file (that just crossed my mind) - I'll try that tomorrow (I think I can unset headings in psql as well - so that will give me a pseudo 'unload/copy' file I think)
On Fri, 2008-09-12 at 12:20 -0400, Sean Davis wrote:
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. What version of Postgres are you using? The docs for the version can give you an idea about syntax. Sean > > 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,
Seems to work okay for me.
Which version of PostgreSQL are you using?
Looks like the query option was introduced in 8.2
I did a sample
copy (SELECT * FROM information_schema.tables WHERE table_schema LIKE
'pg%') to '/temp/pgcat.csv';
'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@retsol.co.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:
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.
|
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:
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
|
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:
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@retsol.co.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@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
|