pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
dumping query results to a csv
8+ messages / 5 participants
[nested] [flat]

* dumping query results to a csv
@ 2005-08-25 22:16  David Durham <ddurham@vailsys.com>
  0 siblings, 0 replies; 8+ messages in thread

From: David Durham @ 2005-08-25 22:16 UTC (permalink / raw)
  To: pg-admin@postgresql.org

This is kind of a pg-admin newbie question, so apologies in advance.

Anyway, I'd like to issue a command that dumps the results of a query to 
a txt file in comma delimited format.  Does PostgreSQL ship with 
something to do this?  I searched the web, but found what appeared to be 
non-free solutions.

Thanks,

Dave



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* dumping query results to a csv
@ 2005-08-25 22:24  David Durham <ddurham@vailsys.com>
  0 siblings, 3 replies; 8+ messages in thread

From: David Durham @ 2005-08-25 22:24 UTC (permalink / raw)
  To: pgsql-admin

This is kind of a pg-admin newbie question, so apologies in advance.

Anyway, I'd like to issue a command that dumps the results of a query to
a txt file in comma delimited format.  Does PostgreSQL ship with
something to do this?  I searched the web, but found what appeared to be
non-free solutions.

Thanks,

Dave




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: dumping query results to a csv
@ 2005-08-25 22:34  Jeff Frost <jeff@frostconsultingllc.com>
  parent: David Durham <ddurham@vailsys.com>
  2 siblings, 1 reply; 8+ messages in thread

From: Jeff Frost @ 2005-08-25 22:34 UTC (permalink / raw)
  To: David Durham <ddurham@vailsys.com>; +Cc: pgsql-admin

David,

You're probably looking for something like this from the psql man page:

        -F separator

        --field-separator separator
               Use  separator  as  the  field  separator. This is equivalent to
               \pset fieldsep or \f.

I would guess -F "," would do the trick.

On Thu, 25 Aug 2005, David Durham wrote:

> This is kind of a pg-admin newbie question, so apologies in advance.
>
> Anyway, I'd like to issue a command that dumps the results of a query to
> a txt file in comma delimited format.  Does PostgreSQL ship with
> something to do this?  I searched the web, but found what appeared to be
> non-free solutions.
>
> Thanks,
>
> Dave
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

-- 
Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: dumping query results to a csv
@ 2005-08-25 22:48  Adi Alurkar <adi@sf.net>
  parent: David Durham <ddurham@vailsys.com>
  2 siblings, 2 replies; 8+ messages in thread

From: Adi Alurkar @ 2005-08-25 22:48 UTC (permalink / raw)
  To: David Durham <ddurham@vailsys.com>; +Cc: pgsql-admin

Greetings,

There is no direct way to achieve what you want, the easiest hack is  
to create a temp table with you query i.e.

create table tmp_foo as select col1, col4, col7 from table1, table2  
where  ....;
copy table tmp_foo to [stdout|<file_name>]

HTH

Adi Alurkar
adi@sf.net


On Aug 25, 2005, at 3:24 PM, David Durham wrote:

> This is kind of a pg-admin newbie question, so apologies in advance.
>
> Anyway, I'd like to issue a command that dumps the results of a  
> query to
> a txt file in comma delimited format.  Does PostgreSQL ship with
> something to do this?  I searched the web, but found what appeared  
> to be
> non-free solutions.
>
> Thanks,
>
> Dave
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: dumping query results to a csv
@ 2005-08-25 23:02  Jeff Frost <jeff@frostconsultingllc.com>
  parent: Jeff Frost <jeff@frostconsultingllc.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Jeff Frost @ 2005-08-25 23:02 UTC (permalink / raw)
  To: David Durham <ddurham@vailsys.com>; +Cc: pgsql-admin

Whoops, should have also mentioned that you want -P format=unaligned like so:

psql -P format=unaligned -F ',' snort <<EOF
select * from iphdr;
EOF

On Thu, 25 Aug 2005, Jeff Frost wrote:

> David,
>
> You're probably looking for something like this from the psql man page:
>
>       -F separator
>
>       --field-separator separator
>              Use  separator  as  the  field  separator. This is equivalent 
> to
>              \pset fieldsep or \f.
>
> I would guess -F "," would do the trick.
>
> On Thu, 25 Aug 2005, David Durham wrote:
>
>> This is kind of a pg-admin newbie question, so apologies in advance.
>> 
>> Anyway, I'd like to issue a command that dumps the results of a query to
>> a txt file in comma delimited format.  Does PostgreSQL ship with
>> something to do this?  I searched the web, but found what appeared to be
>> non-free solutions.
>> 
>> Thanks,
>> 
>> Dave
>> 
>> 
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>> 
>
>

-- 
Jeff Frost, Owner 	<jeff@frostconsultingllc.com>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 650-780-7908	FAX: 650-649-1954



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: dumping query results to a csv
@ 2005-08-25 23:04  Jim C. Nasby <jnasby@pervasive.com>
  parent: Adi Alurkar <adi@sf.net>
  1 sibling, 0 replies; 8+ messages in thread

From: Jim C. Nasby @ 2005-08-25 23:04 UTC (permalink / raw)
  To: Adi Alurkar <adi@sf.net>; +Cc: David Durham <ddurham@vailsys.com>; pgsql-admin

On Thu, Aug 25, 2005 at 03:48:54PM -0700, Adi Alurkar wrote:
> Greetings,
> 
> There is no direct way to achieve what you want, the easiest hack is  
> to create a temp table with you query i.e.
> 
> create table tmp_foo as select col1, col4, col7 from table1, table2  
> where  ....;
> copy table tmp_foo to [stdout|<file_name>]

Can we make this a TODO? It would certainly be handy to be able to COPY
directly from a query.

In the mean time, you can also do something like

SELECT field1 || ',' || field2 || ',' || field3

and capture the output of that to a file, but that's an uglier hack than
the temptable trick.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software        http://pervasive.com        512-569-9461



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: dumping query results to a csv
@ 2005-08-25 23:55  Steve Crawford <scrawford@pinpointresearch.com>
  parent: David Durham <ddurham@vailsys.com>
  2 siblings, 0 replies; 8+ messages in thread

From: Steve Crawford @ 2005-08-25 23:55 UTC (permalink / raw)
  To: David Durham <ddurham@vailsys.com>; pgsql-admin

On Thursday 25 August 2005 3:24 pm, David Durham wrote:
> This is kind of a pg-admin newbie question, so apologies in
> advance.
>
> Anyway, I'd like to issue a command that dumps the results of a
> query to a txt file in comma delimited format.  Does PostgreSQL
> ship with something to do this?  I searched the web, but found what
> appeared to be non-free solutions.

Use heredoc notation to set the format to unaligned, set your field 
separator (and, if necessary, record separator) to whatever you want, 
turn off the footer, and run the output to a file:

psql <any needed connection parameters> --quiet databasename <<EOT
\pset format unaligned
\pset fieldsep ','
\pset footer
\o youroutputfile.csv
select ......
EOT

Or if you prefer everything on the command line:
psql <any needed connection parameters> --quiet --no-align 
--field-separator ',' --pset footer --output youroutputfile.csv 
--command <select .......> databasename

Optionally add \pset tuples-only (first example) or --tuples-only 
(second example) if you do not want the header line with field names 
to be included. Note, if you use tuples only, you don't need to turn 
off the footer separately. You can also use the short versions of all 
the command line switches if you prefer. "man psql"

Cheers,
Steve




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: dumping query results to a csv
@ 2005-08-26 18:22  David Durham <ddurham@vailsys.com>
  parent: Adi Alurkar <adi@sf.net>
  1 sibling, 0 replies; 8+ messages in thread

From: David Durham @ 2005-08-26 18:22 UTC (permalink / raw)
  To: pgsql-admin

Adi Alurkar wrote:
> Greetings,
> 
> There is no direct way to achieve what you want, the easiest hack is  to 
> create a temp table with you query i.e.
> 
> create table tmp_foo as select col1, col4, col7 from table1, table2  
> where  ....;
> copy table tmp_foo to [stdout|<file_name>]
> 
> HTH

I ended up using pgadmin3 -- which has a nice feature for writing the 
contents of a query to file.

Of course, I'm still going to follow this subject and look at other more 
scriptable ways of doing this.

Thanks,

Dave



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2005-08-26 18:22 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-25 22:16 dumping query results to a csv David Durham <ddurham@vailsys.com>
2005-08-25 22:24 dumping query results to a csv David Durham <ddurham@vailsys.com>
2005-08-25 22:34 ` Jeff Frost <jeff@frostconsultingllc.com>
2005-08-25 23:02   ` Jeff Frost <jeff@frostconsultingllc.com>
2005-08-25 22:48 ` Adi Alurkar <adi@sf.net>
2005-08-25 23:04   ` Jim C. Nasby <jnasby@pervasive.com>
2005-08-26 18:22   ` David Durham <ddurham@vailsys.com>
2005-08-25 23:55 ` Steve Crawford <scrawford@pinpointresearch.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox