Thread: CSV mode option for pg_dump
Something someone said on IRC just now triggered a little memory ... I think we should provide an option to have pg_dump work in CSV mode rather than text mode. This probably doesn't have much importance in the case of text dumps, but in custom or tar dumps where you might want to get at individual data members, having an option for CSVs that you want to load into some other product might be nice. This should be a pretty low cost item, I expect (good newbie project?) thoughts? cheers andrew
Andrew Dunstan wrote:
>
> Something someone said on IRC just now triggered a little memory ... I
> think we should provide an option to have pg_dump work in CSV mode
> rather than text mode. This probably doesn't have much importance in the
> case of text dumps, but in custom or tar dumps where you might want to
> get at individual data members, having an option for CSVs that you want
> to load into some other product might be nice.
>
> This should be a pretty low cost item, I expect (good newbie project?)
Uhh... just about any application that can import CSV can import our
dumps. It just tell it the delimiter is a tab.
Joshua D. Drake
>
> thoughts?
>
> cheers
>
> andrew
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
=== The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Joshua D. Drake wrote: > Andrew Dunstan wrote: >> >> Something someone said on IRC just now triggered a little memory ... >> I think we should provide an option to have pg_dump work in CSV mode >> rather than text mode. This probably doesn't have much importance in >> the case of text dumps, but in custom or tar dumps where you might >> want to get at individual data members, having an option for CSVs >> that you want to load into some other product might be nice. >> >> This should be a pretty low cost item, I expect (good newbie project?) > > Uhh... just about any application that can import CSV can import our > dumps. It just tell it the delimiter is a tab. > No it won't, not if there are tabs in the data. Why do you think we did CSV in the first place? Precisely because our dump does *not* work as a general export mechanism for arbitrary data. cheers andrew
Josh, > > This should be a pretty low cost item, I expect (good newbie project?) > > Uhh... just about any application that can import CSV can import our > dumps. It just tell it the delimiter is a tab. Actually, there was an Summer of Code applcation to do this, but with all the other nifty stuff it wasn't accepted -- partly because we weren't sure that we wanted a CSV mode for dumps, partly because this should be a weekend fix, not a 3-month project. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco
Andrew Dunstan <andrew@dunslane.net> writes:
> Something someone said on IRC just now triggered a little memory ... I
> think we should provide an option to have pg_dump work in CSV mode
> rather than text mode. This probably doesn't have much importance in the
> case of text dumps, but in custom or tar dumps where you might want to
> get at individual data members, having an option for CSVs that you want
> to load into some other product might be nice.
This is silly. You'd just COPY the particular table you want, not use
pg_dump. pg_dump's already got an unreasonably large number of options
without adding ones that have essentially zero use. Also, I think there
are sufficient grounds to worry about whether a CSV dump would always
reload correctly --- we already know that that's a poorly thought out
"standard".
regards, tom lane
> No it won't, not if there are tabs in the data.
>
<snipping noise>
Hmmm then would just double quoting the data work? At least in OOCalc
(and IIRC Excel) there is the ability to select a text delimiter.
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: > >> Something someone said on IRC just now triggered a little memory ... I >> think we should provide an option to have pg_dump work in CSV mode >> rather than text mode. This probably doesn't have much importance in the >> case of text dumps, but in custom or tar dumps where you might want to >> get at individual data members, having an option for CSVs that you want >> to load into some other product might be nice. >> > > This is silly. You'd just COPY the particular table you want, not use > pg_dump. pg_dump's already got an unreasonably large number of options > without adding ones that have essentially zero use. Also, I think there > are sufficient grounds to worry about whether a CSV dump would always > reload correctly --- we already know that that's a poorly thought out > "standard". > > > Well, if you have dozens or hundreds of tables it might well be more convenient. As for not reloading - I went to some trouble to make sure that we could reload what we dumped, exactly, unless the force options are used. I might have made a bug in that, but it isn't dependent on the particular CSV format used. Naturally you won't have a use for it, but I suspect others might (in which case they had better speak up ;-) ) I suppose the alternative would be to write a little tool in perl or whatever to do the same thing for you. Maybe a good pgfoundry project. cheers andrew
On Mon, Jun 12, 2006 at 02:15:59PM -0400, Andrew Dunstan wrote: > Naturally you won't have a use for it, but I suspect others might (in > which case they had better speak up ;-) ) I'd bet that those who would find this useful are far more likely to be on -general and not in here. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Here's me speaking up -- I'd definitely use it! As a quick way to pull data into Excel to do basic reports or analysis, a CSV format would be great. Some of our users currently pull data into Excel for quickie analysis, but creating fixed-width data via psql requires them to parse the data and dumping anything via pg_dump with any delimiter (tabs, etc.) usually doesn't work due to the delimiters being embedded in the real data. - Bill > -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of > Andrew Dunstan > Sent: Monday, June 12, 2006 2:16 PM > To: Tom Lane > Cc: PG Hackers > Subject: Re: [HACKERS] CSV mode option for pg_dump > > > Tom Lane wrote: > > Andrew Dunstan <andrew@dunslane.net> writes: > > > >> Something someone said on IRC just now triggered a little > memory ... > >> I > >> think we should provide an option to have pg_dump work in CSV mode > >> rather than text mode. This probably doesn't have much > importance in the > >> case of text dumps, but in custom or tar dumps where you > might want to > >> get at individual data members, having an option for CSVs > that you want > >> to load into some other product might be nice. > >> > > > > This is silly. You'd just COPY the particular table you > want, not use > > pg_dump. pg_dump's already got an unreasonably large number of > > options without adding ones that have essentially zero use. > Also, I > > think there are sufficient grounds to worry about whether a > CSV dump > > would always reload correctly --- we already know that > that's a poorly > > thought out "standard". > > > > > > > > Well, if you have dozens or hundreds of tables it might well be more > convenient. > > As for not reloading - I went to some trouble to make sure > that we could > reload what we dumped, exactly, unless the force options are used. I > might have made a bug in that, but it isn't dependent on the > particular > CSV format used. > > Naturally you won't have a use for it, but I suspect others might (in > which case they had better speak up ;-) ) > > I suppose the alternative would be to write a little tool in perl or > whatever to do the same thing for you. Maybe a good pgfoundry project. > > cheers > > andrew > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
Bill Bartlett wrote:
> Here's me speaking up -- I'd definitely use it! As a quick way to pull
> data into Excel to do basic reports or analysis, a CSV format would be
> great.
Why not just use ODBC?
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Can't -- the main production database is over at a CoLo site with access only available via SSH, and tightly-restricted SSH at that. Generally one of the developers will SSH over to the server, pull out whatever data is needed into a text file via psql or pg_dump, scp the file(s) back here and send them to the user. We're working on implementing a full-blown reporting system to eliminate some of this need, but it's not quite there yet. However, CSV is a very handy format to use to send data to other people (especially "less technical" users), so even in the future the ability to export to CSV would be handy. - Bill > -----Original Message----- > From: Joshua D. Drake [mailto:jd@commandprompt.com] > Sent: Monday, June 12, 2006 4:15 PM > To: Bill Bartlett > Cc: 'Andrew Dunstan'; 'Tom Lane'; 'PG Hackers' > Subject: Re: [HACKERS] CSV mode option for pg_dump > > > Bill Bartlett wrote: > > Here's me speaking up -- I'd definitely use it! As a > quick way to pull > > data into Excel to do basic reports or analysis, a CSV > format would be > > great. > > Why not just use ODBC? > > Joshua D. Drake > -- > > === The PostgreSQL Company: Command Prompt, Inc. === > Sales/Support: +1.503.667.4564 || 24x7/Emergency: > +1.800.492.2240 > Providing the most comprehensive PostgreSQL solutions > since 1997 > http://www.commandprompt.com/ > >
Already on TODO:
pg_dump:
o %Add CSV output format
---------------------------------------------------------------------------
Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
> > Something someone said on IRC just now triggered a little memory ... I
> > think we should provide an option to have pg_dump work in CSV mode
> > rather than text mode. This probably doesn't have much importance in the
> > case of text dumps, but in custom or tar dumps where you might want to
> > get at individual data members, having an option for CSVs that you want
> > to load into some other product might be nice.
>
> This is silly. You'd just COPY the particular table you want, not use
> pg_dump. pg_dump's already got an unreasonably large number of options
> without adding ones that have essentially zero use. Also, I think there
> are sufficient grounds to worry about whether a CSV dump would always
> reload correctly --- we already know that that's a poorly thought out
> "standard".
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
-- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Bill Bartlett wrote: > Can't -- the main production database is over at a CoLo site with access > only available via SSH, and tightly-restricted SSH at that. Generally > one of the developers will SSH over to the server, pull out whatever > data is needed into a text file via psql or pg_dump, scp the file(s) > back here and send them to the user. ODBC over an SSH tunnnel?
Matthew T. OConnor said: > Bill Bartlett wrote: >> Can't -- the main production database is over at a CoLo site with >> access only available via SSH, and tightly-restricted SSH at that. >> Generally one of the developers will SSH over to the server, pull out >> whatever data is needed into a text file via psql or pg_dump, scp the >> file(s) back here and send them to the user. > > ODBC over an SSH tunnnel? I wish I could understand why people are so keen to make other people turn handsprings in order to avoid a feature which, as Bruce points out, is already on the TODO list, and which, by my 10 minute analysis, would involve almost trivial code impact and risk. If this involved major impact I might understand, but it really doesn't. I know many people work in a Postgres only world. I wish everybody did, and then we could just forget about things like CSV. They don't, so we can't. I think I have said this before, but I'll say it again. From time to time people thank me for things I have done for Postgres. The two things that stand out BY FAR on the list of these are CSV import/export and dollar quoting. This is a widely used feature. cheers andrew
Andrew Dunstan wrote: > Matthew T. OConnor said: > > Bill Bartlett wrote: > >> Can't -- the main production database is over at a CoLo site with > >> access only available via SSH, and tightly-restricted SSH at that. > >> Generally one of the developers will SSH over to the server, pull out > >> whatever data is needed into a text file via psql or pg_dump, scp the > >> file(s) back here and send them to the user. > > > > ODBC over an SSH tunnnel? > > > I wish I could understand why people are so keen to make other people turn > handsprings in order to avoid a feature which, as Bruce points out, is > already on the TODO list, and which, by my 10 minute analysis, would involve > almost trivial code impact and risk. If this involved major impact I might > understand, but it really doesn't. > > I know many people work in a Postgres only world. I wish everybody did, and > then we could just forget about things like CSV. They don't, so we can't. > > I think I have said this before, but I'll say it again. From time to time > people thank me for things I have done for Postgres. The two things that > stand out BY FAR on the list of these are CSV import/export and dollar > quoting. This is a widely used feature. I think the bottom line is that "ease of use" isn't as high enough on the project's priority list as you (and others) think it should be. I personally feel as you do that we should value "ease of use" more. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
"Andrew Dunstan" <andrew@dunslane.net> writes:
> I wish I could understand why people are so keen to make other people turn
> handsprings in order to avoid a feature which, as Bruce points out, is
> already on the TODO list, and which, by my 10 minute analysis, would involve
> almost trivial code impact and risk. If this involved major impact I might
> understand, but it really doesn't.
Supporting all of the CSV options in pg_dump would involve major bloat
in its option set, and it already has far too many options. If it were
just a matter of adding a "--csv" switch I wouldn't be complaining, but
there are half a dozen more sub-options, and it seems like every time we
turn around someone is finding a reason for another one. Propagating
all that cruft through pg_dump would be a PITA, and updating it to track
future additions would be too.
Furthermore, the entire rationale for the feature is predicated on the
claim that programs other than pg_restore might find it useful. But
this conveniently ignores the fact that if there are any such programs
in existence, what this will really do is BREAK them, because they won't
be able to cope with all the variants that pass for CSV.
My opinions would be less negative if I thought that CSV were a
well-defined format that would never change. I don't believe that it
has either property, however, and so I'm against letting it get into our
dump file format. I think we'll just live to regret it if we do.
regards, tom lane
Good point. The number of CSV options would be hard to support for pg_dump. Any thoughts from anyone on how to do that cleanly? Could we just support the default behavior? --------------------------------------------------------------------------- Tom Lane wrote: > "Andrew Dunstan" <andrew@dunslane.net> writes: > > I wish I could understand why people are so keen to make other people turn > > handsprings in order to avoid a feature which, as Bruce points out, is > > already on the TODO list, and which, by my 10 minute analysis, would involve > > almost trivial code impact and risk. If this involved major impact I might > > understand, but it really doesn't. > > Supporting all of the CSV options in pg_dump would involve major bloat > in its option set, and it already has far too many options. If it were > just a matter of adding a "--csv" switch I wouldn't be complaining, but > there are half a dozen more sub-options, and it seems like every time we > turn around someone is finding a reason for another one. Propagating > all that cruft through pg_dump would be a PITA, and updating it to track > future additions would be too. > > Furthermore, the entire rationale for the feature is predicated on the > claim that programs other than pg_restore might find it useful. But > this conveniently ignores the fact that if there are any such programs > in existence, what this will really do is BREAK them, because they won't > be able to cope with all the variants that pass for CSV. > > My opinions would be less negative if I thought that CSV were a > well-defined format that would never change. I don't believe that it > has either property, however, and so I'm against letting it get into our > dump file format. I think we'll just live to regret it if we do. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Tue, Jun 13, 2006 at 10:20:53AM -0400, Bruce Momjian wrote: > > Good point. The number of CSV options would be hard to support for > pg_dump. Any thoughts from anyone on how to do that cleanly? Could we > just support the default behavior? What this tells me is that we need a tool somewhere between psql and pg_dump, say, pgquery. It's sole purpose in life is to generate output from various queries. Because it's a seperate tool there's no question of psql or pg_dump being able to parse them. While you're at it, you could add modules to support many different output styles, like CSV, XML, Excel format, HTML, etc. This I beleive would take the load off psql to provide many different output styles, as well as the load off pg_dump to produce parsable-by-third-party output. Thoughts? Side note: In my experience Excel happily slurps up tab delimited output, so I'm not sure why all of this is an issue in the first place. Have a ncie day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
On Jun 13 10:20, Bruce Momjian wrote: > > Good point. The number of CSV options would be hard to support for > pg_dump. Any thoughts from anyone on how to do that cleanly? Could we > just support the default behavior? IMHO, it might be better if we'd support a syntax like pg_dump --csv=opt0,para0:opt2,opt3 This can save us from the pg_dump parameter pollution a little bit. Furthermore, I think CSV format for the dump files can be maintained better under an external project. (pgFoundry?) By this way, main developers will be able to cope with their own core problems while other users/developers can contribute on the CSV code easily. And if any user will ever want to get CSV functionality in the pg_dump, he/she will just issue a --csv parameter (with the above syntax) and pg_dump will make a suitable dlopen() call for the related (CSV) module. Anyway, this is just an idea for modularity; but the main thing I try to underline is to give pg_dump a module functionality for similar problems. Regards.
Tom Lane wrote: >there are half a dozen more sub-options, and it seems like every time we >turn around someone is finding a reason for another one. > > This is a bit unfair. The feature was introduced in 8.0, and slightly enhanced in 8.1. There have not been any additional CSV features this release cycle unless my memory is worse than I thought, and I at least have said previously that I will be resistant to the addition of further CSV options. My thoughts regarding options for pg_dump was actually to provide a much smaller set than the full set available with COPY, specifically to provide for using a single rather than a double quote char, and optional header lines - no alternate escape or delimiter, and no FORCE QUOTE (FORCE NOT NULL isn't relevant as it is only useful for non-postgres derived data). At least that would be a reasonable starting point, and would I believe cater for the vast majority of uses. cheers andrew
Bruce Momjian wrote:
> Good point. The number of CSV options would be hard to support for
> pg_dump. Any thoughts from anyone on how to do that cleanly? Could we
> just support the default behavior?
Although I don't see a real need for the feature, I do think that if we
were to support "1" (well two if you include the already tab delimited)
csv output it would be a large amount of bloat.
Perhaps we could pick "1" output, say comma delimted with quoted fields?
"foo","bar ","baz"
Joshua D. Drake
>
> ---------------------------------------------------------------------------
>
> Tom Lane wrote:
>> "Andrew Dunstan" <andrew@dunslane.net> writes:
>>> I wish I could understand why people are so keen to make other people turn
>>> handsprings in order to avoid a feature which, as Bruce points out, is
>>> already on the TODO list, and which, by my 10 minute analysis, would involve
>>> almost trivial code impact and risk. If this involved major impact I might
>>> understand, but it really doesn't.
>> Supporting all of the CSV options in pg_dump would involve major bloat
>> in its option set, and it already has far too many options. If it were
>> just a matter of adding a "--csv" switch I wouldn't be complaining, but
>> there are half a dozen more sub-options, and it seems like every time we
>> turn around someone is finding a reason for another one. Propagating
>> all that cruft through pg_dump would be a PITA, and updating it to track
>> future additions would be too.
>>
>> Furthermore, the entire rationale for the feature is predicated on the
>> claim that programs other than pg_restore might find it useful. But
>> this conveniently ignores the fact that if there are any such programs
>> in existence, what this will really do is BREAK them, because they won't
>> be able to cope with all the variants that pass for CSV.
>>
>> My opinions would be less negative if I thought that CSV were a
>> well-defined format that would never change. I don't believe that it
>> has either property, however, and so I'm against letting it get into our
>> dump file format. I think we'll just live to regret it if we do.
>>
>> regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Volkan YAZICI wrote: >On Jun 13 10:20, Bruce Momjian wrote: > > >>Good point. The number of CSV options would be hard to support for >>pg_dump. Any thoughts from anyone on how to do that cleanly? Could we >>just support the default behavior? >> >> > >IMHO, it might be better if we'd support a syntax like > > pg_dump --csv=opt0,para0:opt2,opt3 > >This can save us from the pg_dump parameter pollution a little bit. > >Furthermore, I think CSV format for the dump files can be maintained >better under an external project. (pgFoundry?) By this way, main >developers will be able to cope with their own core problems while >other users/developers can contribute on the CSV code easily. And if >any user will ever want to get CSV functionality in the pg_dump, >he/she will just issue a --csv parameter (with the above syntax) and >pg_dump will make a suitable dlopen() call for the related (CSV) >module. Anyway, this is just an idea for modularity; but the main >thing I try to underline is to give pg_dump a module functionality for >similar problems. > > > There are some problems with this, though: . FORCE QUOTE is table specific, and COPY will barf if you name a column that isn't on the table. Providing for this option would involve lots more code in pg_dump, as we'd have to filter the list according to the column names in each table. . specifying arbitrary chars for quote, escape and delimiter could be tricky from the command line, especially if you want to specify a tab delimiter or backslash escape. cheers andrew
Martijn van Oosterhout wrote: > >Side note: In my experience Excel happily slurps up tab delimited >output, so I'm not sure why all of this is an issue in the first place. > > > I guess you experience doesn't run to data that has embedded tabs, for example. There really is a reason we did this in the first place, and it wasn't for fun. cheers andrew
On Mon, 2006-06-12 at 16:28 -0400, Bill Bartlett wrote: > Can't -- the main production database is over at a CoLo site with access > only available via SSH, and tightly-restricted SSH at that. Generally > one of the developers will SSH over to the server, pull out whatever > data is needed into a text file via psql or pg_dump, scp the file(s) > back here and send them to the user. I don't get it. If you can use psql then you already have csv support. psql -c 'COPY pg_class TO STDOUT WITH CSV' postgres > pg_class.csv > > -----Original Message----- > > From: Joshua D. Drake [mailto:jd@commandprompt.com] > > Sent: Monday, June 12, 2006 4:15 PM > > To: Bill Bartlett > > Cc: 'Andrew Dunstan'; 'Tom Lane'; 'PG Hackers' > > Subject: Re: [HACKERS] CSV mode option for pg_dump > > > > > > Bill Bartlett wrote: > > > Here's me speaking up -- I'd definitely use it! As a > > quick way to pull > > > data into Excel to do basic reports or analysis, a CSV > > format would be > > > great. > > > > Why not just use ODBC? > > > > Joshua D. Drake > > -- > > > > === The PostgreSQL Company: Command Prompt, Inc. === > > Sales/Support: +1.503.667.4564 || 24x7/Emergency: > > +1.800.492.2240 > > Providing the most comprehensive PostgreSQL solutions > > since 1997 > > http://www.commandprompt.com/ > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > --
On Jun 13, 2006, at 7:34 AM, Martijn van Oosterhout wrote: > > What this tells me is that we need a tool somewhere between psql and > pg_dump, say, pgquery. It's sole purpose in life is to generate output > from various queries. Because it's a seperate tool there's no question > of psql or pg_dump being able to parse them. > > While you're at it, you could add modules to support many different > output styles, like CSV, XML, Excel format, HTML, etc. > > This I beleive would take the load off psql to provide many different > output styles, as well as the load off pg_dump to produce > parsable-by-third-party output. > > Thoughts? Perl+DBD::Pg+CPAN does almost all of this already. Lots of support for countless different output formats, and mostly fairly well battle- tested. I suspect that a perl script to do all that would be dominated by commandline option parsing, as all the hard work is in existing modules. Would that be adequate, or do we really want to reimplement and maintain all the output format complexity in our own code, in C? Cheers, Steve
Rod Taylor wrote: > On Mon, 2006-06-12 at 16:28 -0400, Bill Bartlett wrote: >> Can't -- the main production database is over at a CoLo site with access >> only available via SSH, and tightly-restricted SSH at that. Generally >> one of the developers will SSH over to the server, pull out whatever >> data is needed into a text file via psql or pg_dump, scp the file(s) >> back here and send them to the user. > > I don't get it. If you can use psql then you already have csv support. > > psql -c 'COPY pg_class TO STDOUT WITH CSV' postgres > pg_class.csv If you data looks like this: foo barbaz bing You are o.k. You have three columns, tab delimited. However if you data looks like this: foo bar baz bing You have a problem. foo is one column bar and baz are a single column bing is a single column How does excel know that bar baz is a single column? It doesn't because you told it to delimit on tabs and thus you have four columns as far as Excel is concerned. An alternative although I don't know what kind of headaches it would cause is to have a text delimiter as well as a field delimter, e.g; "foo" "bar baz" "bing" Sincerely, Joshua D. Drake > >>> -----Original Message----- >>> From: Joshua D. Drake [mailto:jd@commandprompt.com] >>> Sent: Monday, June 12, 2006 4:15 PM >>> To: Bill Bartlett >>> Cc: 'Andrew Dunstan'; 'Tom Lane'; 'PG Hackers' >>> Subject: Re: [HACKERS] CSV mode option for pg_dump >>> >>> >>> Bill Bartlett wrote: >>>> Here's me speaking up -- I'd definitely use it! As a >>> quick way to pull >>>> data into Excel to do basic reports or analysis, a CSV >>> format would be >>>> great. >>> Why not just use ODBC? >>> >>> Joshua D. Drake >>> -- >>> >>> === The PostgreSQL Company: Command Prompt, Inc. === >>> Sales/Support: +1.503.667.4564 || 24x7/Emergency: >>> +1.800.492.2240 >>> Providing the most comprehensive PostgreSQL solutions >>> since 1997 >>> http://www.commandprompt.com/ >>> >>> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 1: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >> -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
From what I gather, the CSV format dump would only contain data.I think pg_dump is the friend of pg_restore. It dumps everything including user defined functions, types, schemas etc. CSV does not fit with this. Besides, people will probably want to dump into CSV the result of any query, to load it into excel, not just the full contents of a table. So, why not create a separate tool, someone suggested pg_query for that, I second it.This tool would take a query and format options, and would output a file in whatever format chosen by the user (CSV, COPY format, xml, whatever) A script language (python) can be used, which will significantly shorten development times and allow easy modularity, as it is easier to add a module to a python program than a C program.I would vote for Python because I love it and it has a very good postgres adapter (psycopg2) which knows how to convers every postgres type to a native language type (yes, even multidimensional arrays of BOX get converted). And it's really fast at retrieving large volumes of data. So you have a stable, fast tool for backup and restore (pg_dump) and a rapidly evolving, user-friendly and extendable tool for exporting data, and everyone is happy. Mr Momijan talks about adding modular functionality to pg_dump. Is it really necessary ? What is the objective ? Is it to reuse code in pg_dump ? I guess not ; if a user wants to dump, for instance, all the tables in a schema, implementing this logic in python is only a few lines of code (select from information_schema...) To be realistic, output format modules should be written in script languages. Noone sane is eager to do string manipulation in C. Thus these modules would have to somehow fit with pg_dump, maybe with a pipe or something. This means designing another protocol. Reimplementing in a scripting langage the parts of pg_dump which will be reused by this project (mainly, enumerating tables and stuff) will be far easier. Just look. Python 2.4.2 (#1, Mar 30 2006, 14:34:35) [GCC 3.4.4 (Gentoo 3.4.4-r1, ssp-3.4.4-1.0, pie-8.7.8)] on linux2 Type "help", "copyright", "credits" or "license" for more information. ...opens a db connection... >>> c.execute( "SELECT * FROM test.csv" ) >>> data = c.fetchall() >>> data [[1, datetime.date(2006, 6, 13), 'this\tcontains\ttabulations'], [2, datetime.date(2006, 6, 13), "this'contains'quotes"], [3, datetime.date(2006, 6, 13), 'this"contains"double quotes']] >>> import csv, sys >>> c = csv.writer( sys.stdout, dialect = csv.excel ) >>> c.writerows( data ) 1,2006-06-13,this contains tabulations 2,2006-06-13,this'contains'quotes 3,2006-06-13,"this""contains""double quotes"
PFC wrote: > > From what I gather, the CSV format dump would only contain data. > I think pg_dump is the friend of pg_restore. It dumps everything > including user defined functions, types, schemas etc. CSV does not > fit with this. > > This is just nonsense. There is not the slightest reason that CSV data cannot be embedded in a text dump nor exist as the data members of a custom or tar dump with the corresponding COPY statements modified accordingly. Really, let's get the facts straight, please. cheers andrew
> > Would that be adequate, or do we really want to reimplement and maintain > all > the output format complexity in our own code, in C? I think the point is that we should provide a native implementation because not everyone is crazy enough to use perl (blatant jab ;)). I would never expect a customer to write a perl or python script just to get their data in what is widely considered a standard business format that can be imported by their userland application. The people on the hackers list, are NOT the target for this feature. The people on general, admin and novice are. Sincerely, Joshua D. Drake > > Cheers, > Steve > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
> From: Rod Taylor [mailto:pg@rbt.ca] > Sent: Tuesday, June 13, 2006 11:31 AM > > > On Mon, 2006-06-12 at 16:28 -0400, Bill Bartlett wrote: > > Can't -- the main production database is over at a CoLo site with > > access only available via SSH, and tightly-restricted SSH at that. > > Generally one of the developers will SSH over to the > server, pull out > > whatever data is needed into a text file via psql or > pg_dump, scp the > > file(s) back here and send them to the user. > > I don't get it. If you can use psql then you already have csv support. > > psql -c 'COPY pg_class TO STDOUT WITH CSV' postgres > pg_class.csv Ah - RTF-UPDATED-M on my part. Most of my systems are still in PG 7.4.x databases so I tend to stick with the 7.x docs, and I didn't notice the "WITH CSV" option added in 8.0. That, plus temp tables, will be very useful. However, I also agree with the need for a new "pg_query / pg_export" program. A program geared solely towards exporting the results of a query would allow many of the options that are needed for the ever-growing variety of output formats (XML, CSV, HTML, XHTML, etc.) and details for each format without needing to clutter up pg_dump with things that really having nothing to do with backing up and restoring data. It could also allow a large range of options related to getting data out (where, order by), many of which have also been discussed for pg_dump recently. - Bill > > > > -----Original Message----- > > > From: Joshua D. Drake [mailto:jd@commandprompt.com] > > > Sent: Monday, June 12, 2006 4:15 PM > > > To: Bill Bartlett > > > Cc: 'Andrew Dunstan'; 'Tom Lane'; 'PG Hackers' > > > Subject: Re: [HACKERS] CSV mode option for pg_dump > > > > > > > > > Bill Bartlett wrote: > > > > Here's me speaking up -- I'd definitely use it! As a > > > quick way to pull > > > > data into Excel to do basic reports or analysis, a CSV > > > format would be > > > > great. > > > > > > Why not just use ODBC? > > > > > > Joshua D. Drake > > > -- > > > > > > === The PostgreSQL Company: Command Prompt, Inc. === > > > Sales/Support: +1.503.667.4564 || 24x7/Emergency: > > > +1.800.492.2240 > > > Providing the most comprehensive PostgreSQL solutions > > > since 1997 > > > http://www.commandprompt.com/ > > > > > > > -- >
On Jun 13, 2006, at 9:47 AM, Joshua D. Drake wrote: >> Would that be adequate, or do we really want to reimplement and >> maintain all >> the output format complexity in our own code, in C? > > I think the point is that we should provide a native implementation > because not everyone is crazy enough to use perl (blatant jab ;)). > I would never expect a customer to write a perl or python script > just to get their data in what is widely considered a standard > business format that can be imported by their userland application. That wasn't what I was discussing, on two levels. Firstly, I wasn't suggesting that the end user write anything, secondly I was talking about the other output formats discussed (Excel, HTML...) rather than just CSV. > > The people on the hackers list, are NOT the target for this > feature. The people on general, admin and novice are. I was referring to the other export formats mentioned (Excel, HTML...). We already support CSV export of single tables with the bundled software, don't we? Cheers, Steve
Steve Atkins wrote: > > Would that be adequate, or do we really want to reimplement and > maintain all > the output format complexity in our own code, in C? > > Code to produce CSVs is there, now, today, and has been since 8.0. That is *not* what is at issue here. If you want to debate whether or not it should be in the backend you are 2 years too late. The ONLY question here is about whether or not to have it enabled as an option in pg_dump. try these and see the interesting results: COPY pg_class TO '/tmp/pg_class.csv' CSV; or in psql \copy pg_class to '/tmp/pg_class.csv' csv As for XML which was also mentioned, you should be aware that there is a Google Summer of Code project to implement SQL/XML, so watch this space. (As for why that needs to be in the backend, see previous discussions) cheers andrew
pg_dump CSV TODO item removed until we come up with something everyone can agree on. --------------------------------------------------------------------------- Joshua D. Drake wrote: > > > > Would that be adequate, or do we really want to reimplement and maintain > > all > > the output format complexity in our own code, in C? > > I think the point is that we should provide a native implementation > because not everyone is crazy enough to use perl (blatant jab ;)). I > would never expect a customer to write a perl or python script just to > get their data in what is widely considered a standard business format > that can be imported by their userland application. > > The people on the hackers list, are NOT the target for this feature. The > people on general, admin and novice are. > > Sincerely, > > Joshua D. Drake > > > > > > > > Cheers, > > Steve > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > -- > > === The PostgreSQL Company: Command Prompt, Inc. === > Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 > Providing the most comprehensive PostgreSQL solutions since 1997 > http://www.commandprompt.com/ > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote:
>pg_dump CSV TODO item removed until we come up with something everyone
>can agree on.
>
>
>
That's a pity.
Just to show you how little is involved in what I was suggesting, a
prototype patch is attached - it's 182 lines of context diff, which is
pretty small for a new feature. It took me about an hour to write and I
have tested it against the regression db in both text and binary dump
modes, where it works without a hitch.
cheers
andrew
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.438
diff -c -r1.438 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 9 Jun 2006 19:46:09 -0000 1.438
--- src/bin/pg_dump/pg_dump.c 14 Jun 2006 00:32:03 -0000
***************
*** 113,118 ****
--- 113,123 ----
/* flag to turn on/off dollar quoting */
static int disable_dollar_quoting = 0;
+ /* flag to control if using CSv */
+ static bool use_csv = false;
+
+ /* holder for CSV options */
+ static PQExpBuffer csv_opts;
static void help(const char *progname);
static NamespaceInfo *findNamespace(Oid nsoid, Oid objoid);
***************
*** 251,256 ****
--- 256,265 ----
{"disable-triggers", no_argument, &disable_triggers, 1},
{"use-set-session-authorization", no_argument, &use_setsessauth, 1},
+ /* long options with no short version */
+ {"csv", no_argument, NULL ,2},
+ {"csv-option",required_argument, NULL, 3},
+
{NULL, 0, NULL, 0}
};
int optindex;
***************
*** 285,290 ****
--- 294,301 ----
}
}
+ csv_opts = createPQExpBuffer();
+
while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:oOp:RsS:t:uU:vWxX:Z:",
long_options, &optindex)) != -1)
{
***************
*** 419,424 ****
--- 430,462 ----
break;
/* This covers the long options equivalent to -X xxx. */
+ case 2: /* csv */
+ use_csv = true;
+ break;
+
+ case 3: /* csv-option */
+ if (strcmp(optarg, "singlequote") == 0)
+ {
+ appendPQExpBuffer(csv_opts,"QUOTE AS '''' ");
+ }
+ else if (strcmp(optarg, "tabdelimiter") == 0)
+ {
+ appendPQExpBuffer(csv_opts,"DELIMITER AS E'\\t' ");
+ }
+ else if (strcmp(optarg, "header") == 0)
+ {
+ appendPQExpBuffer(csv_opts,"HEADER ");
+ }
+ else
+ {
+ fprintf(stderr,
+ _("%s: invalid csv option -- %s\n"),
+ progname, optarg);
+ fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
+ exit(1);
+ }
+
+
case 0:
break;
***************
*** 463,468 ****
--- 501,518 ----
exit(1);
}
+ if ( use_csv == true && dumpInserts == true)
+ {
+ write_msg(NULL, "INSERT (-d, -D) and CSV (--csv) options cannot be used together\n");
+ exit(1);
+ }
+
+ if ( use_csv == false && strlen(csv_opts->data) > 0)
+ {
+ write_msg(NULL, "You must specify --csv to use --csv-option\n");
+ exit(1);
+ }
+
/* open the output file */
switch (format[0])
{
***************
*** 714,719 ****
--- 764,771 ----
" use SESSION AUTHORIZATION commands instead of\n"
" OWNER TO commands\n"));
+ printf(_(" --csv use CSV mode\n"));
+ printf(_(" --csv-option=opt one of header, tabdelimiter or singlequote\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
printf(_(" -p, --port=PORT database server port number\n"));
***************
*** 881,898 ****
if (oids && hasoids)
{
! appendPQExpBuffer(q, "COPY %s %s WITH OIDS TO stdout;",
fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
classname),
column_list);
}
else
{
! appendPQExpBuffer(q, "COPY %s %s TO stdout;",
fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
classname),
column_list);
}
res = PQexec(g_conn, q->data);
check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
PQclear(res);
--- 933,955 ----
if (oids && hasoids)
{
! appendPQExpBuffer(q, "COPY %s %s WITH OIDS TO stdout",
fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
classname),
column_list);
}
else
{
! appendPQExpBuffer(q, "COPY %s %s TO stdout",
fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
classname),
column_list);
}
+ if (use_csv)
+ {
+ appendPQExpBuffer(q, " CSV %s", csv_opts->data);
+ }
+ appendPQExpBuffer(q, ";");
res = PQexec(g_conn, q->data);
check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
PQclear(res);
***************
*** 1139,1147 ****
/* must use 2 steps here 'cause fmtId is nonreentrant */
appendPQExpBuffer(copyBuf, "COPY %s ",
fmtId(tbinfo->dobj.name));
! appendPQExpBuffer(copyBuf, "%s %sFROM stdin;\n",
fmtCopyColumnList(tbinfo),
(tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
copyStmt = copyBuf->data;
}
else
--- 1196,1208 ----
/* must use 2 steps here 'cause fmtId is nonreentrant */
appendPQExpBuffer(copyBuf, "COPY %s ",
fmtId(tbinfo->dobj.name));
! appendPQExpBuffer(copyBuf, "%s %s",
fmtCopyColumnList(tbinfo),
(tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
+ appendPQExpBuffer(copyBuf, "FROM stdin");
+ if (use_csv)
+ appendPQExpBuffer(copyBuf, " CSV %s", csv_opts->data);
+ appendPQExpBuffer(copyBuf, ";\n");
copyStmt = copyBuf->data;
}
else
Andrew Dunstan <andrew@dunslane.net> writes:
> This is just nonsense. There is not the slightest reason that CSV data
> cannot be embedded in a text dump nor exist as the data members of a
> custom or tar dump with the corresponding COPY statements modified
> accordingly.
Well, the really *core* question here is whether we trust the stability
of the CSV format definition (and code) enough to want to rely on it for
data dump/restore purposes. I'm still a few years away from that,
myself. AFAICT the raison d'etre of the CSV code is "emit whatever it
takes to satisfy this, that, and the other broken Microsoft application".
That's fine as an export tool, but as a dump/reload tool, nyet. If you
put it in pg_dump you're just handing neophytes another foot-gun.
regards, tom lane
Tom Lane said: > Andrew Dunstan <andrew@dunslane.net> writes: >> This is just nonsense. There is not the slightest reason that CSV data >> cannot be embedded in a text dump nor exist as the data members of a >> custom or tar dump with the corresponding COPY statements modified >> accordingly. > > Well, the really *core* question here is whether we trust the stability > of the CSV format definition (and code) enough to want to rely on it > for data dump/restore purposes. I'm still a few years away from that, > myself. AFAICT the raison d'etre of the CSV code is "emit whatever it > takes to satisfy this, that, and the other broken Microsoft > application". That's fine as an export tool, but as a dump/reload tool, > nyet. If you put it in pg_dump you're just handing neophytes another > foot-gun. > Well, I'm staggered. Really. The CSV definition and its lack of formality is a complete red herring in this, as are references to Microsoft. The real issue in what you say here is your suggestion that we might not be able to reproduce the input in some cases via a COPY CSV round trip. If that is so it's a bug of our (probably my) making, and must be fixed. I assert that it is not so. In fact all the tests I did during development and since were premised on recovering the input exactly. The only CSV option that breaks things in that respect is FORCE NOT NULL, and it is designed for data coming in from a non Postgres source, so it's not relevant here. Even FORCE QUOTE won't break things because it never quotes a null value, and the only semantic significance to us of quoting is the null-ness of the value. If the code is broken then it should be discoverable by test or code analysis. There is no need to refer to any other application or standard. So if you or anyone think there is a case that will not reproduce the data exactly when the same CSV options are used for output and input, I challenge you or them to provide a single example. You say you're a few years away from trusting the code. Well, it's not so huge that it's beyond analysis, and I'll be happy to explain anything that puzzles you. Perhaps more importantly, it has been in use now for 18 months. We discovered one problem with embedded line feeds very early in the 8.0 release cycle, and fixed it. After that I have not heard of a single problem. And I assure you this code is widely used. It probably isn't used much as a round trip mechanism, probably in part because we haven't provided it as a pg_dump option. So maybe we have a chicken/egg scenario here. We do have some round trip regression tests in the copy test, and those can be beefed up if necessary to increase your confidence level. I'm happy to debate details, but general assertions of "we can't trust this code" don't seem worth much to me. cheers andrew
I agree with Andrew. If there are bugs in CSV, then we should fix them, not avoid give users this usability improvement. I know I have analyzed the code and fixed a few problems with it in the past. As far as pg_dump, I am thinking the most flexible solution would be to allow an arbitrary WITH clause to be added to COPY, like pg_dump --copy-with "CSV" This would allow not only CSV control, but specification of non-CSV options if desired. --------------------------------------------------------------------------- Andrew Dunstan wrote: > Tom Lane said: > > Andrew Dunstan <andrew@dunslane.net> writes: > >> This is just nonsense. There is not the slightest reason that CSV data > >> cannot be embedded in a text dump nor exist as the data members of a > >> custom or tar dump with the corresponding COPY statements modified > >> accordingly. > > > > Well, the really *core* question here is whether we trust the stability > > of the CSV format definition (and code) enough to want to rely on it > > for data dump/restore purposes. I'm still a few years away from that, > > myself. AFAICT the raison d'etre of the CSV code is "emit whatever it > > takes to satisfy this, that, and the other broken Microsoft > > application". That's fine as an export tool, but as a dump/reload tool, > > nyet. If you put it in pg_dump you're just handing neophytes another > > foot-gun. > > > > > Well, I'm staggered. Really. > > The CSV definition and its lack of formality is a complete red herring in > this, as are references to Microsoft. > > The real issue in what you say here is your suggestion that we might not be > able to reproduce the input in some cases via a COPY CSV round trip. If that > is so it's a bug of our (probably my) making, and must be fixed. I assert > that it is not so. In fact all the tests I did during development and since > were premised on recovering the input exactly. The only CSV option that > breaks things in that respect is FORCE NOT NULL, and it is designed for data > coming in from a non Postgres source, so it's not relevant here. Even FORCE > QUOTE won't break things because it never quotes a null value, and the only > semantic significance to us of quoting is the null-ness of the value. > > If the code is broken then it should be discoverable by test or code > analysis. There is no need to refer to any other application or standard. So > if you or anyone think there is a case that will not reproduce the data > exactly when the same CSV options are used for output and input, I challenge > you or them to provide a single example. > > You say you're a few years away from trusting the code. Well, it's not so > huge that it's beyond analysis, and I'll be happy to explain anything that > puzzles you. Perhaps more importantly, it has been in use now for 18 months. > We discovered one problem with embedded line feeds very early in the 8.0 > release cycle, and fixed it. After that I have not heard of a single > problem. And I assure you this code is widely used. > > It probably isn't used much as a round trip mechanism, probably in part > because we haven't provided it as a pg_dump option. So maybe we have a > chicken/egg scenario here. We do have some round trip regression tests in > the copy test, and those can be beefed up if necessary to increase your > confidence level. > > I'm happy to debate details, but general assertions of "we can't trust this > code" don't seem worth much to me. > > cheers > > andrew > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I agree with Andrew. If there are bugs in CSV, then we should fix them,
> not avoid give users this usability improvement.
The case for it being a usability improvement seems very weak to me;
no one has yet demonstrated an actual use-case where someone would pull
CSV data out of pg_dump output instead of just dumping the table
directly with COPY. Now the "anti" case is admittedly hypothetical:
I'm supposing that we will eventually be bitten by portability problems
with CSV-style dumps not being loadable into future versions. But given
the weak nature of the "pro" case, I think we should be conservative
and not take that risk.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I agree with Andrew. If there are bugs in CSV, then we should fix them, > > not avoid give users this usability improvement. > > The case for it being a usability improvement seems very weak to me; > no one has yet demonstrated an actual use-case where someone would pull > CSV data out of pg_dump output instead of just dumping the table > directly with COPY. Now the "anti" case is admittedly hypothetical: > I'm supposing that we will eventually be bitten by portability problems > with CSV-style dumps not being loadable into future versions. But given > the weak nature of the "pro" case, I think we should be conservative > and not take that risk. Well, I saw little request for COPY CSV here, but IRC users were reporting a lot of interest, and feedback from the release that added it showed it was a major feature, so just because we haven't see use-case here doesn't mean it doesn't exist. As was stated before, the use-case for this is by people we don't normally have contact with. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> As was stated before, the use-case for this is by people we don't
> normally have contact with.
Or who don't exist.
Once again: give me a plausible use-case for pulling this data out of a
pg_dump output file. You'd need data-massaging code anyway just to
extract the data, so why not expect that code to convert to CSV or
whatever other format you might want? If you can think of use-cases like
this, why do you think the destination format is necessarily CSV and not
something else? If it is something else, adding a CSV option to pg_dump
makes it *harder* not easier to write that massaging code, because now
it's got to cope with N dump formats not one.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > As was stated before, the use-case for this is by people we don't > > normally have contact with. > > Or who don't exist. > > Once again: give me a plausible use-case for pulling this data out of a > pg_dump output file. You'd need data-massaging code anyway just to > extract the data, so why not expect that code to convert to CSV or > whatever other format you might want? If you can think of use-cases like > this, why do you think the destination format is necessarily CSV and not > something else? If it is something else, adding a CSV option to pg_dump > makes it *harder* not easier to write that massaging code, because now > it's got to cope with N dump formats not one. I don't have to think of a use case. I trusted the people who said we needed CSV, so I trust them again if they say doing pg_dump with CSV would be a good idea. Also, my suggestion of --copy-with would allow CSV and other format modifications with minimal code and complexity. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Tue, Jun 13, 2006 at 09:14:48AM -0700, Joshua D. Drake wrote:
> >I don't get it. If you can use psql then you already have csv support.
> >
> >psql -c 'COPY pg_class TO STDOUT WITH CSV' postgres > pg_class.csv
>
> If you data looks like this:
>
> foo barbaz bing
>
> You are o.k. You have three columns, tab delimited.
>
> However if you data looks like this:
>
> foo bar baz bing
>
> You have a problem.
I'm not exactly sure what you're getting at here:
postgres=# create temp table x (a text, b text, c text);
CREATE TABLE
postgres=# insert into x values ('"blah"', 'hello world', 'hmmm
postgres'# ');
INSERT 0 1
postgres=# \copy x to stdout csv
"""blah""",hello world,"hmmm
"
> An alternative although I don't know what kind of headaches it would
> cause is to have a text delimiter as well as a field delimter, e.g;
Postgresql already delimits CSV fields as required, so I'm not sure what
you're asking here...
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
On Wed, Jun 14, 2006 at 10:55:04AM -0400, Bruce Momjian wrote:
> > Once again: give me a plausible use-case for pulling this data out of a
> > pg_dump output file. You'd need data-massaging code anyway just to
> > extract the data, so why not expect that code to convert to CSV or
> > whatever other format you might want? If you can think of use-cases like
> > this, why do you think the destination format is necessarily CSV and not
> > something else? If it is something else, adding a CSV option to pg_dump
> > makes it *harder* not easier to write that massaging code, because now
> > it's got to cope with N dump formats not one.
>
> I don't have to think of a use case. I trusted the people who said we
> needed CSV, so I trust them again if they say doing pg_dump with CSV
> would be a good idea.
I think the point is that it's hard to imagine a use case for CSV
support in pg_dump that isn't already better served by using psql. If
you add it to pg_dump, people have to write special code to extract it
anyway, so why can't they handle the tab delimited themselves.
OTOH, if you use psql you get straightforward CSV wit no garbage at
the beginning or end, just straight, raw, CSV.
$ psql postgres -c '\copy pg_namespace to stdout csv'
pg_toast,10,
pg_temp_1,10,
pg_catalog,10,"{kleptog=UC/kleptog,=U/kleptog}"
public,10,"{kleptog=UC/kleptog,=UC/kleptog}"
information_schema,10,"{kleptog=UC/kleptog,=U/kleptog}"
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
"Andrew Dunstan" <andrew@dunslane.net> writes: > The CSV definition and its lack of formality is a complete red herring in > this, as are references to Microsoft. I think then you've missed the real issue. > The real issue in what you say here is your suggestion that we might not be > able to reproduce the input in some cases via a COPY CSV round trip. No that's not the suggestion. The question is: what do you do when the next version of Excel comes out and can't parse your CSV files any more? If you change your format to match then you won't be able to parse old pg_dump files any more. You'll end up with an option for each csv variant and then how does the user know which option to use to read a given pg_dump archive? And before you ask, yes, virtually every version of Excel has changed its csv file format. I think the problem here is that pg_dump is serving double duty as a postgres->postgres tool and some people using it as a kind of batch COPY. What benefit does it buy you over a script that runs COPY for each table if that's what you want? -- greg
Bruce Momjian wrote: > >As was stated before, the use-case for this is by people we don't >normally have contact with. > > > I do think we need a use case for what we do. The main use case seems to me to be where you are exporting a whole database or most of it with a very large number of tables, and it is convenient to have all the CSVs created for you rather than have to make them manually one at a time. You could get these out of, say, a tar format dump very easily. I said near the beginning of this that a pgfoundry project to create a tool for this might be an alternative way to go. If that's the consensus then Ok. I just bristle a bit at the suggestion that we might not get back what we started with from a CSV dump, because we can, AFAIK. cheers andrew
Andrew Dunstan wrote: > > > Bruce Momjian wrote: > > > > >As was stated before, the use-case for this is by people we don't > >normally have contact with. > > > > > > > > I do think we need a use case for what we do. > > The main use case seems to me to be where you are exporting a whole > database or most of it with a very large number of tables, and it is > convenient to have all the CSVs created for you rather than have to make > them manually one at a time. You could get these out of, say, a tar > format dump very easily. > > I said near the beginning of this that a pgfoundry project to create a > tool for this might be an alternative way to go. If that's the consensus > then Ok. I just bristle a bit at the suggestion that we might not get > back what we started with from a CSV dump, because we can, AFAIK. For me, the use case would be, what format do I want a dump in if it is for long-term storage? Do I want it in a PostgreSQL-native format, or in a more universal format that can be loaded into PostgreSQL tomorrow, and perhaps loaded into some other database, with modification, ten years from now. I just had that issue on my home system for file system backups, going from cpio to ustar (POSIX.1-1988 / IEEE Std1003.2), but it seems that POSIX.1-2001 would be best if my operating system supported it. (Perhaps cpio was better?) Anyway, I never thought there would be a large demand for COPY CSV, but obviously there is, so I have concluded that other people's environment and skills are different enough from my own that I am willing to accept the idea there is a use case when I don't understand it. I will let the people who work in those environments make that decision. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Greg Stark wrote: >And before you ask, yes, virtually every version of Excel has changed its csv >file format. > > > > I'd be interested to hear of Excel variants we haven't catered for - our CSV parsing is some of the most flexible and complete around, IMNSHO. BTW, I suspect we won't see too much movement on this front in future, given the moves on the XML front. cheers andrew
I wrote: > > The main use case seems to me to be where you are exporting a whole > database or most of it with a very large number of tables, and it is > convenient to have all the CSVs created for you rather than have to > make them manually one at a time. You could get these out of, say, a > tar format dump very easily. > > I just noticed that the data members all have \. and some blank lines at the end, so wash that out. We now return you to normal -hacking cheers andrew