Thread: COPY formatting
Hi,
in TODO is item: "* Allow dump/load of CSV format". I don't thinkit's clean idea. Why CSV and why not something other?
:-)
A why not allow to users full control of the format by they ownfunction. It means something like:COPY tablename [ (
column[, ...] ) ] TO { 'filename' | STDOUT } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER
[AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] [ FORMAT funcname ] ] ^^^^^^^^^^^^^^^^
The formatting function API can be pretty simple:
text *my_copy_format(text *attrdata, int direction, int nattrs, int attr, oid attrtype, oid relation)
-- it's pseudocode of course, it should be use standard fmgrinterface.It's probably interesting for non-binary COPY
version.Comments?
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
Karel Zak wrote:
> Hi,
>
> in TODO is item: "* Allow dump/load of CSV format". I don't think
> it's clean idea. Why CSV and why not something other? :-)
>
> A why not allow to users full control of the format by they own
> function. It means something like:
>
> COPY tablename [ ( column [, ...] ) ]
> TO { 'filename' | STDOUT }
> [ [ WITH ]
> [ BINARY ]
> [ OIDS ]
> [ DELIMITER [ AS ] 'delimiter' ]
> [ NULL [ AS ] 'null string' ]
> [ FORMAT funcname ] ]
> ^^^^^^^^^^^^^^^^
>
> The formatting function API can be pretty simple:
>
> text *my_copy_format(text *attrdata, int direction,
> int nattrs, int attr, oid attrtype, oid relation)
>
> -- it's pseudocode of course, it should be use standard fmgr
> interface.
>
> It's probably interesting for non-binary COPY version.
>
> Comments?
>
> Karel
>
Karel,
This seems to be an excellent idea.
People have already asked for many different formats.
Usually I recommend them to use "psql -c "COPY ..." dbname | awk ...".
Since Windows will be supported soon, it will be hard to pipe data to a
useful program (awk, sed, ...). Maybe this feature would help a lot in
this case.
Regards,
Hans
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
Hans-Jürgen Schönig wrote:
> Karel Zak wrote:
>
>> Hi,
>>
>> in TODO is item: "* Allow dump/load of CSV format". I don't think
>> it's clean idea. Why CSV and why not something other? :-)
>> A why not allow to users full control of the format by they own
>> function. It means something like:
>>
>> COPY tablename [ ( column [, ...] ) ]
>> TO { 'filename' | STDOUT }
>> [ [ WITH ] [ BINARY ]
>> [ OIDS ]
>> [ DELIMITER [ AS ] 'delimiter' ]
>> [ NULL [ AS ] 'null string' ]
>> [ FORMAT funcname ] ]
>> ^^^^^^^^^^^^^^^^
>> The formatting
>> function API can be pretty simple:
>>
>> text *my_copy_format(text *attrdata, int direction, int
>> nattrs, int attr, oid attrtype, oid relation)
>>
>> -- it's pseudocode of course, it should be use standard fmgr
>> interface.
>>
>> It's probably interesting for non-binary COPY version.
>>
>> Comments?
>>
>> Karel
>>
>
>
> Karel,
>
> This seems to be an excellent idea.
> People have already asked for many different formats.
> Usually I recommend them to use "psql -c "COPY ..." dbname | awk ...".
> Since Windows will be supported soon, it will be hard to pipe data to a
> useful program (awk, sed, ...). Maybe this feature would help a lot in
> this case.
>
Could a CSV-generating function be provided with the distribution then?
Regards,
Fernando
Karel Zak <zakkr@zf.jcu.cz> writes:
> The formatting function API can be pretty simple:
> text *my_copy_format(text *attrdata, int direction,
> int nattrs, int attr, oid attrtype, oid relation)
This seems like it could only reasonably be implemented as a C function.
I can't really imagine the average user of COPY wanting to write C in
preference to, say, an external perl script. What's the real use-case
for the feature?
regards, tom lane
Karel Zak wrote:
> Hi,
>
> in TODO is item: "* Allow dump/load of CSV format". I don't think
> it's clean idea. Why CSV and why not something other? :-)
>
> A why not allow to users full control of the format by they own
> function. It means something like:
>
> COPY tablename [ ( column [, ...] ) ]
> TO { 'filename' | STDOUT }
> [ [ WITH ]
> [ BINARY ]
> [ OIDS ]
> [ DELIMITER [ AS ] 'delimiter' ]
> [ NULL [ AS ] 'null string' ]
> [ FORMAT funcname ] ]
> ^^^^^^^^^^^^^^^^
>
> The formatting function API can be pretty simple:
>
> text *my_copy_format(text *attrdata, int direction,
> int nattrs, int attr, oid attrtype, oid relation)
>
> -- it's pseudocode of course, it should be use standard fmgr
> interface.
>
> It's probably interesting for non-binary COPY version.
>
>
>
Interesting ... The alternative might be an external program to munge
CSVs and whatever other format people want to support and then call the
exisiting COPY- either in bin or contrib. I have seen lots of people
wanting to import CSVs, and that's even before we get a Windows port.
cheers
andrew
Tom Lane wrote: > Karel Zak <zakkr@zf.jcu.cz> writes: > >> The formatting function API can be pretty simple: >> text *my_copy_format(text *attrdata, int direction, >> int nattrs, int attr, oid attrtype, oid relation) > > > This seems like it could only reasonably be implemented as a C function. > I can't really imagine the average user of COPY wanting to write C in > preference to, say, an external perl script. What's the real use-case > for the feature? > That is why I suggested providing a pre-written/pre-compiled/installed function for CSV (call it CSV?). Advanced users could still write their own as people can write many other things if they know their ways.
> > >> > > That is why I suggested providing a pre-written/pre-compiled/installed > function for CSV (call it CSV?). Advanced users could still write > their own as people can write many other things if they know their ways. > As someone who just went through a whole truckload of crap getting delimited files parsed from MSSQL to PostgreSQL. I believe yes this would be great thing. We ended up using plPython with the CSV module. Sincerely, Joshua Drake > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
I deal with this daily in a cygwin environment. I wrote a simple c++ program where I hardcoded the input file name/location and output file name/location. I strip the quotation marks out where they are used for identifying text fields and change the comma's used as CSV's to pipes. I use a combination of bash scripting to execute the c++ program and then Perl to execute a stored procedure. I am new to Perl so I have not yet considered migrating it all into Perl. The dos2unix tools in cygwin always messed up the first character of the first line. I thought the real issue with the copy function and CSVs was that it did not like the use of quotations around the fields to identify text fields. For a true Windows port handling MS Excel files in their native format would be a goal I would hope. If your api could handle that then I would agree with your method. On Wed, 2004-03-17 at 22:14, Joshua D. Drake wrote: > > > > > >> > > > > That is why I suggested providing a pre-written/pre-compiled/installed > > function for CSV (call it CSV?). Advanced users could still write > > their own as people can write many other things if they know their ways. > > > > As someone who just went through a whole truckload of crap getting > delimited files parsed from MSSQL to PostgreSQL. I believe yes this > would be great thing. We ended up using plPython with the CSV module. > > Sincerely, > > Joshua Drake > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 7: don't forget to increase your free space map settings > >
On Wed, Mar 17, 2004 at 11:02:38AM -0500, Tom Lane wrote: > Karel Zak <zakkr@zf.jcu.cz> writes: > > The formatting function API can be pretty simple: > > text *my_copy_format(text *attrdata, int direction, > > int nattrs, int attr, oid attrtype, oid relation) > > This seems like it could only reasonably be implemented as a C function. Why? I said it's pseudo code. It should use standard fmgr API likeevery other PostgreSQL function or is it problem and Ioverlooksomething? It must to support arbitrary programming language and notC only. > I can't really imagine the average user of COPY wanting to write C in > preference to, say, an external perl script. What's the real use-case > for the feature? Don't hardcode any format to PostgreSQL, be open for others formats. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
On Wed, Mar 17, 2004 at 10:11:48AM -0500, Fernando Nasser wrote: > Could a CSV-generating function be provided with the distribution then? I think yes. Any other format suggestion? Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
On Thu, Mar 18, 2004 at 07:48:40AM +0100, Karel Zak wrote: > On Wed, Mar 17, 2004 at 11:02:38AM -0500, Tom Lane wrote: > > Karel Zak <zakkr@zf.jcu.cz> writes: > > > The formatting function API can be pretty simple: > > > text *my_copy_format(text *attrdata, int direction, > > > int nattrs, int attr, oid attrtype, oid relation) > > > > This seems like it could only reasonably be implemented as a C function. > > Why? I said it's pseudo code. It should use standard fmgr API like > every other PostgreSQL function or is it problem and I overlook > something? It must to support arbitrary programming language and not > C only. Well, I look over the COPY code and best will start with hardcodedversion, but make it modular in code and if all willright we can thinkabout some interface for others formats definition. OK? It's pity that main idea of current COPY is based on separated linesand it is not more common interface for streamingdata between FE and BE. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
To be honest this idea strikes me as overkill - over
engineering. While there is a clear need for proper CSV import
(i.e. just setting DELIMITER to ',' doesn't work due to ','s in
strings) I cannot see how this would prove useful, or who would use
it?
While i have done a lot of messing around reading/writing the binary
format (and been stung by changes in that format) if you are using
this format then you're 99% likely to be in control of the
incoming/outgoing data and thus able to format to your wishes outwith
COPY.
Something else in the TODO regarding COPY is XML import/export, and
for this to be supported in your proposed implementation the function
would need to be passed in a heap more information.
L.
Karel Zak writes:> > Hi,> > in TODO is item: "* Allow dump/load of CSV format". I don't think> it's clean idea. Why
CSVand why not something other? :-) > > A why not allow to users full control of the format by they own> function. It
meanssomething like:> > COPY tablename [ ( column [, ...] ) ]> TO { 'filename' | STDOUT }> [ [ WITH ] >
[ BINARY ]> [ OIDS ]> [ DELIMITER [ AS ] 'delimiter' ]> [ NULL [ AS ] 'null string'
]> [ FORMAT funcname ] ]> ^^^^^^^^^^^^^^^^> > The
formattingfunction API can be pretty simple:> > text *my_copy_format(text *attrdata, int direction, > int
nattrs,int attr, oid attrtype, oid relation)> > -- it's pseudocode of course, it should be use standard fmgr>
interface.> > It's probably interesting for non-binary COPY version.> > Comments?> > Karel
On Thu, Mar 18, 2004 at 09:29:03AM +0000, Lee Kindness wrote: > To be honest this idea strikes me as overkill - over > engineering. It was suggestion, maybe you're right :-) > While i have done a lot of messing around reading/writing the binary > format (and been stung by changes in that format) if you are using > this format then you're 99% likely to be in control of the > incoming/outgoing data and thus able to format to your wishes outwith > COPY. I partly agree.But.. there is possible write directly final file by backend withoutdata transfer to client. If we wantto support this feature we needcontrol output format by server... And.. I can image format that is use for BE/FE data transfer only andnot for some final data presentation. For example compression of datastream from/to BE without PostgreSQL protocol change. > Something else in the TODO regarding COPY is XML import/export, and > for this to be supported in your proposed implementation the function > would need to be passed in a heap more information. Yes, very probably some struct with all COPY information and formatspecific stuff. Tom was right that in this caseit will C functionsonly. As I said I will try implement it without user defined functioncall for format conversion, but I will do it modular and in future wecan create some interface for user defined formats. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
Lee Kindness wrote: >To be honest this idea strikes me as overkill - over >engineering. While there is a clear need for proper CSV import >(i.e. just setting DELIMITER to ',' doesn't work due to ','s in >strings) I cannot see how this would prove useful, or who would use >it? > > > I agree. My modest proposal for handling CSVs would be to extend the DELIMITER parameter to allow up to 3 characters - separator, quote and escape. Escape would default to the quote char and the quote char would default to unspecified. This would involve no grammar changes and fairly isolated and small code changes, I believe. In the most common CSV cases you would just use $$,"$$ or $$,'$$. :-) COPY is basically line/tuple oriented, and that alone would exclude many file formats (e.g. imagine wanting to import a spreadsheet where each worksheet was the table name and the first row on each worksheet was the field names - I have seen such beasts more than once). If we want a general facility for loading and exporting foreign file formats, I really believe that is the province of a utility program rather than a database engine function. The reason in my mind for making CSV a special case is that it is very easy to do and so often asked for. (I used to set parsing CSVs as a basic programming exercise - it is amazing how many way people find to get it wrong). cheers andrew
Andrew Dunstan wrote: > Lee Kindness wrote: > > >To be honest this idea strikes me as overkill - over > >engineering. While there is a clear need for proper CSV import > >(i.e. just setting DELIMITER to ',' doesn't work due to ','s in > >strings) I cannot see how this would prove useful, or who would use > >it? > > > > > > > I agree. My modest proposal for handling CSVs would be to extend the > DELIMITER parameter to allow up to 3 characters - separator, quote and > escape. Escape would default to the quote char and the quote char would > default to unspecified. This would involve no grammar changes and fairly > isolated and small code changes, I believe. In the most common CSV cases > you would just use $$,"$$ or $$,'$$. :-) > > COPY is basically line/tuple oriented, and that alone would exclude many > file formats (e.g. imagine wanting to import a spreadsheet where each > worksheet was the table name and the first row on each worksheet was the > field names - I have seen such beasts more than once). If we want a > general facility for loading and exporting foreign file formats, I > really believe that is the province of a utility program rather than a > database engine function. > > The reason in my mind for making CSV a special case is that it is very > easy to do and so often asked for. > > (I used to set parsing CSVs as a basic programming exercise - it is > amazing how many way people find to get it wrong). I like the separator, quote, and escape idea. It allows variety without requiring folks to code in C. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Karel Zak <zakkr@zf.jcu.cz> writes:
>> On Wed, Mar 17, 2004 at 11:02:38AM -0500, Tom Lane wrote:
>>> Karel Zak <zakkr@zf.jcu.cz> writes:
>>>> This seems like it could only reasonably be implemented as a C function.
>>
>> Why? I said it's pseudo code. It should use standard fmgr API like
>> every other PostgreSQL function or is it problem and I overlook
>> something? It must to support arbitrary programming language and not
>> C only.
Sure, but the question is whether the *stuff it has to do* can
reasonably be coded in anything but C. Why are you passing in a
relation OID, if not for lookups in relcache entries that are simply
not accessible above the C level? (Don't tell me you want the function
to do a bunch of actual SELECTs from system catalogs for every line
of the copy...)
Passing in a relation OID is probably a bad idea anyway, as it ties this
API to the assumption that COPY is only for complete relations. There's
been talk before of allowing a SELECT result to be presented via the
COPY protocol, for instance. What might be a more usable API is
COPY OUT: function formatter_out(text[]) returns text
COPY IN: function formatter_in(text) returns text[]
where the text array is either the results of or the input to the
per-column datatype I/O routines. This makes it explicit that the
formatter's job is solely to determine the column-level wrapping and
unwrapping of the data. I'm assuming here that there is no good reason
for the formatter to care about the specific datatypes involved; can you
give a counterexample?
> It's pity that main idea of current COPY is based on separated lines
> and it is not more common interface for streaming data between FE and BE.
Yeah, that was another concern I had. This API would let the formatter
control line-level layout but it would not eliminate the hard-wired
significance of newline. What's worse, there isn't any clean way to
deal with reading quoted newlines --- the formatter can't really replace
the default quoting rules if the low-level code is going to decide
whether a newline is quoted or not.
We could possibly solve that by specifying that the text output or input
(respectively) is the complete line sent to or from the client,
including newline or whatever other line-level formatting you are using.
This still leaves the problem of how the low-level COPY IN code knows
what is a complete line to pass off to the formatter_in routine. We
could possibly fix this by adding a second input-control routine
function formatter_linelength(text) returns integer
which is defined to return -1 if the input isn't a complete line yet
(i.e., read some more data, append to the buffer, and try again), or
>= 0 to indicate that the first N bytes of the buffer represent a
complete line to be passed off to formatter_in. I don't see a way to
combine formatter_in and formatter_linelength into a single function
without relying on "out" parameters, which would again confine the
feature to format functions written in C.
It's a tad annoying that we need two functions for input. One way that
we could still keep the COPY option syntax to be justFORMAT csv
is to create an arbitrary difference in the signatures of the input
functions. Then we could have coexisting functionscsv(text[]) returns textcsv(text) returns text[]csv(text, ...)
returnsint
that are referenced by "FORMAT csv".
regards, tom lane
Andrew Dunstan <andrew@dunslane.net> writes:
> Lee Kindness wrote:
>> To be honest this idea strikes me as overkill - over
>> engineering.
>>
> I agree. My modest proposal for handling CSVs would be to extend the
> DELIMITER parameter to allow up to 3 characters - separator, quote and
> escape. Escape would default to the quote char and the quote char would
> default to unspecified.
I could go with that too. The question here is do we have any popular
use-cases that aren't solved by that extension, but could be solved by
simple user-level data formatting functions? I'm not real eager to add
such a feature as an "if we build it they will come" speculation, but
if anyone can point to solid use-cases besides handling CSV, then it
probably is worth doing.
regards, tom lane
Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: > > Lee Kindness wrote: > >> To be honest this idea strikes me as overkill - over > >> engineering. > >> > > I agree. My modest proposal for handling CSVs would be to extend the > > DELIMITER parameter to allow up to 3 characters - separator, quote and > > escape. Escape would default to the quote char and the quote char would > > default to unspecified. > > I could go with that too. The question here is do we have any popular > use-cases that aren't solved by that extension, but could be solved by > simple user-level data formatting functions? I'm not real eager to add > such a feature as an "if we build it they will come" speculation, but > if anyone can point to solid use-cases besides handling CSV, then it > probably is worth doing. The thing I liked about Andrew's idea is that it even covers escape quoting for CVS, which might change from implementation to implentation, and it is flexible without requiring C coding. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Tom Lane <tgl@sss.pgh.pa.us> writes: > I could go with that too. The question here is do we have any popular > use-cases that aren't solved by that extension, but could be solved by > simple user-level data formatting functions? I'm not real eager to add > such a feature as an "if we build it they will come" speculation, but > if anyone can point to solid use-cases besides handling CSV, then it > probably is worth doing. (I can't believe I'm saying this, but) It seems like xml output would be the use case you're looking for. -- greg
Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> I could go with that too. The question here is do we have any popular
>> use-cases that aren't solved by that extension, but could be solved by
>> simple user-level data formatting functions?
> (I can't believe I'm saying this, but) It seems like xml output would be the
> use case you're looking for.
Does that fall into the category of stuff that could be supported by the
kind of API we're talking about? I should think that XML would need a
much more global view of the data, not just line-by-line reformatting.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Does that fall into the category of stuff that could be supported by the > kind of API we're talking about? I should think that XML would need a > much more global view of the data, not just line-by-line reformatting. I saw it the other way around. The only reason I saw for writing code in COPY directly was if it needed a more global view of the data. Any line-by-line reformatting could just be done with a perl one-liner. If it can't do that what's the advantage? Speed? -- greg
On Thu, Mar 18, 2004 at 09:36:17AM -0500, Andrew Dunstan wrote: > Lee Kindness wrote: > > >To be honest this idea strikes me as overkill - over > >engineering. While there is a clear need for proper CSV import > >(i.e. just setting DELIMITER to ',' doesn't work due to ','s in > >strings) I cannot see how this would prove useful, or who would use > >it? > > > > > > > I agree. My modest proposal for handling CSVs would be to extend the > DELIMITER parameter to allow up to 3 characters - separator, quote and > escape. Escape would default to the quote char and the quote char would > default to unspecified. This would involve no grammar changes and fairly > isolated and small code changes, I believe. In the most common CSV cases > you would just use $$,"$$ or $$,'$$. :-) I look at backend COPY code and it will better clean up this "party ofglobal static values" before add something to this code. (My priviteopinion of course). The problem with CSV is that it will correctly work with new protocolonly. Because old versions of clients are newline sensitive. And CSVcan contains newline in by quotation marks defined attributes: "John", "Smith", "The White House1600 Pennsylvania Avenue NWWashington, DC 20500", "male", "open source software office"Itis one record. It's difficult to say it :-), but your DELIMITER idea is better than mysuggested API. Andrew, goahead. I thought about some data streaming,but COPY is probably bad place for it. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
On Thu, Mar 18, 2004 at 10:16:36AM -0500, Tom Lane wrote: > Passing in a relation OID is probably a bad idea anyway, as it ties this > API to the assumption that COPY is only for complete relations. There's > been talk before of allowing a SELECT result to be presented via the > COPY protocol, for instance. What might be a more usable API is > > COPY OUT: > function formatter_out(text[]) returns text > COPY IN: > function formatter_in(text) returns text[] > > where the text array is either the results of or the input to the > per-column datatype I/O routines. This makes it explicit that the > formatter's job is solely to determine the column-level wrapping and > unwrapping of the data. I'm assuming here that there is no good reason > for the formatter to care about the specific datatypes involved; can you > give a counterexample? The idea was put maximum information about tuple to formatter, and whatwill formatter do with this information is a formatterproblem. > > It's pity that main idea of current COPY is based on separated lines > > and it is not more common interface for streaming data between FE and BE. > > Yeah, that was another concern I had. This API would let the formatter > control line-level layout but it would not eliminate the hard-wired > significance of newline. What's worse, there isn't any clean way to > deal with reading quoted newlines --- the formatter can't really replace > the default quoting rules if the low-level code is going to decide > whether a newline is quoted or not. I think latest protocol version works with blocks of data and no withlines and client PQputCopyData() returns a block --only docs says thatit is row of table. > We could possibly solve that by specifying that the text output or input > (respectively) is the complete line sent to or from the client, > including newline or whatever other line-level formatting you are using. > This still leaves the problem of how the low-level COPY IN code knows > what is a complete line to pass off to the formatter_in routine. We > could possibly fix this by adding a second input-control routine > > function formatter_linelength(text) returns integer > > which is defined to return -1 if the input isn't a complete line yet But formatter_linelength() will need some context information Ithink. The others words some struct with formatter specific internaldata. And for more difficult formats like XML you need some otherscontext data (parser data)too. Maybe there can be some global exported struct (like for triggers) andfunctions that is written in C can use it. It meansfor simple formatslike CSV you can use non-C functions and for formats like XML you canuse C functions. And if itwill intereting for PL developers they canadd support for access to this structs to their languages. > (i.e., read some more data, append to the buffer, and try again), or > >= 0 to indicate that the first N bytes of the buffer represent a > complete line to be passed off to formatter_in. I don't see a way to > combine formatter_in and formatter_linelength into a single function > without relying on "out" parameters, which would again confine the > feature to format functions written in C. > It's a tad annoying that we need two functions for input. One way that > we could still keep the COPY option syntax to be just > FORMAT csv > is to create an arbitrary difference in the signatures of the input > functions. Then we could have coexisting functions > csv(text[]) returns text > csv(text) returns text[] > csv(text, ...) returns int > that are referenced by "FORMAT csv". It sounds good, but I think we both not full sure about it now, right?CSV support will probably better add by DELIMITERextension. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
andrew@dunslane.net (Andrew Dunstan) writes:
> Karel Zak wrote:
>
>> Hi,
>>
>> in TODO is item: "* Allow dump/load of CSV format". I don't think
>> it's clean idea. Why CSV and why not something other? :-)
>>
>> A why not allow to users full control of the format by they own
>> function. It means something like:
>> COPY tablename [ ( column [, ...] ) ]
>> TO { 'filename' | STDOUT }
>> [ [ WITH ] [ BINARY ]
>> [ OIDS ]
>> [ DELIMITER [ AS ] 'delimiter' ]
>> [ NULL [ AS ] 'null string' ]
>> [ FORMAT funcname ] ]
>> ^^^^^^^^^^^^^^^^
>> The formatting
>> function API can be pretty simple:
>>
>> text *my_copy_format(text *attrdata, int direction, int
>> nattrs, int attr, oid attrtype, oid relation)
>>
>> -- it's pseudocode of course, it should be use standard fmgr
>> interface.
>> It's probably interesting for non-binary COPY version.
>
> Interesting ... The alternative might be an external program to munge
> CSVs and whatever other format people want to support and then call
> the exisiting COPY- either in bin or contrib. I have seen lots of
> people wanting to import CSVs, and that's even before we get a Windows
> port.
I know Jan Wieck has been working on something like this, with a bit
of further smarts...
- By having, alongside, a table definition, the table can be created concurrently;
- A set of mapping functions can be used, so that if, for instance, the program generating the data was Excel, and you
havea field with values like 37985, 38045, or 38061, they can respectively be mapped to '2004-01-01', '2004-03-01',
and'2004-03-17';
- It can load whatever data is loadable, and use Ethernet-like backoffs when it encounters bad records so that it
loadsall the data that is good, and leaves a bundle of 'crud' that is left over.
He had been prototyping it in Tcl; I'm not sure how far a port to C
has gotten. It looked pretty neat; it sure seems better to put the
"cleverness" in userspace than to try to increase the complexity of
the postmaster...
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://cbbrowne.com/info/linuxxian.html
Have you heard of the new Macsyma processor? It has three instructions --
LOAD, STORE, and SKIP IF INTEGRABLE.
Karel Zak wrote: > The problem with CSV is that it will correctly work with new protocol > only. Because old versions of clients are newline sensitive. And CSV > can contains newline in by quotation marks defined attributes: > > "John", "Smith", "The White House > 1600 Pennsylvania Avenue NW > Washington, DC 20500", "male", "open source software office" > > It is one record. > (Long Live President Smith!) I have never seen such a beast, and if I had been asked off the cuff would have said that it was probably illegal, except that I know of no standard actually defining the format. Perhaps others (Joshua Drake or Josh Berkus?) have wider experience. I think in any case we should ignore those for now and handle the straightforward case. I *have* seen monstrosities like fields that do not begin with the quote character but then break into a quote, e.g.: 1,2,a,123"abc""def",6,7,8 > > It's difficult to say it :-), but your DELIMITER idea is better than my > suggested API. Andrew, go ahead. I thought about some data streaming, > but COPY is probably bad place for it. > > > Just to be clear, I have no current plans for doing this. I have about 3 pg related things I am working on, in addition to doing paid work so I can eat! If I do it it will not be for quite a while. There are some wrinkles, though, concerning the interaction of CSV's notion of escaping and COPY's notion of escaping. If someone want to undertake this I can flesh those out in a further email. Someone was asking the other day about a newbie or student project - this might be a good one (impact relatively isolated, very easy to test) although possibly not as sexy as some might like. cheers andrew
Karel Zak <zakkr@zf.jcu.cz> writes:
>>> It's pity that main idea of current COPY is based on separated lines
>>> and it is not more common interface for streaming data between FE and BE.
>>
>> Yeah, that was another concern I had. This API would let the formatter
>> control line-level layout but it would not eliminate the hard-wired
>> significance of newline. What's worse, there isn't any clean way to
>> deal with reading quoted newlines --- the formatter can't really replace
>> the default quoting rules if the low-level code is going to decide
>> whether a newline is quoted or not.
> I think latest protocol version works with blocks of data and no with
> lines and client PQputCopyData() returns a block -- only docs says that
> it is row of table.
But you can't assume that the client will send blocks that are
semantically significant. For instance, if psql is reading a file to
send with \copy, how's it going to know how the file is formatted?
It's just gonna send disk-block-sized messages, and the backend has to
discover the semantic boundaries for itself.
> It sounds good, but I think we both not full sure about it now, right?
> CSV support will probably better add by DELIMITER extension.
Yeah, without people beating on our door for such a hook, it seems like
Andrew's DELIMITER idea is the best thing to do for now.
regards, tom lane
Andrew Dunstan wrote: > > > Karel Zak wrote: > >> The problem with CSV is that it will correctly work with new protocol >> only. Because old versions of clients are newline sensitive. And CSV >> can contains newline in by quotation marks defined attributes: >> >> "John", "Smith", "The White House >> 1600 Pennsylvania Avenue NW >> Washington, DC 20500", "male", "open source software office" >> >> It is one record. > > > (Long Live President Smith!) > > I have never seen such a beast, Export from a spreadsheet where people have formated the cell with the address on it. Regards, Fernando
On Fri, Mar 19, 2004 at 09:39:58AM -0500, Tom Lane wrote: > Karel Zak <zakkr@zf.jcu.cz> writes: > >>> It's pity that main idea of current COPY is based on separated lines > >>> and it is not more common interface for streaming data between FE and BE. > >> > >> Yeah, that was another concern I had. This API would let the formatter > >> control line-level layout but it would not eliminate the hard-wired > >> significance of newline. What's worse, there isn't any clean way to > >> deal with reading quoted newlines --- the formatter can't really replace > >> the default quoting rules if the low-level code is going to decide > >> whether a newline is quoted or not. > > > I think latest protocol version works with blocks of data and no with > > lines and client PQputCopyData() returns a block -- only docs says that > > it is row of table. > > But you can't assume that the client will send blocks that are > semantically significant. For instance, if psql is reading a file to > send with \copy, how's it going to know how the file is formatted? And what \n in attibutes data in CSV? I think CSV format doesn't usesome escape for newline char. It means psql with\copy cannot be surewith CSV. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
Karel Zak <zakkr@zf.jcu.cz> writes:
> The problem with CSV is that it will correctly work with new protocol
> only. Because old versions of clients are newline sensitive.
Why? The client-side code doesn't have any real say over the meaning of
the data, at least not in psql-class clients. I suppose a client app
that tries to interpret the data could get confused, but psql sure
doesn't do that.
regards, tom lane
Fernando Nasser wrote: > Andrew Dunstan wrote: > >> >> >> Karel Zak wrote: >> >>> The problem with CSV is that it will correctly work with new protocol >>> only. Because old versions of clients are newline sensitive. And CSV >>> can contains newline in by quotation marks defined attributes: >>> >>> "John", "Smith", "The White House >>> 1600 Pennsylvania Avenue NW >>> Washington, DC 20500", "male", "open source software office" >>> >>> It is one record. >> >> >> >> (Long Live President Smith!) >> >> I have never seen such a beast, > > > Export from a spreadsheet where people have formated the cell with the > address on it. > Well, I just tried with OpenOffice on my RH9 box, and it translated the line breaks in the cell into spaces in the CSV. When I replaced them with line breaks in a text editor and reloaded it treated them as separate rows. I don't have a Windows box handy on which I can test Excel's behaviour. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> There are some wrinkles, though, concerning the interaction of CSV's
> notion of escaping and COPY's notion of escaping. If someone want to
> undertake this I can flesh those out in a further email.
Please do that, so that the info is in the archives in case someone else
wants to tackle the project.
regards, tom lane
I wrote: > Fernando Nasser wrote: > >>> [snip re multiline CSV fields] >>> >>> I have never seen such a beast, >> >> >> >> Export from a spreadsheet where people have formated the cell with >> the address on it. >> > > Well, I just tried with OpenOffice on my RH9 box, and it translated > the line breaks in the cell into spaces in the CSV. When I replaced > them with line breaks in a text editor and reloaded it treated them as > separate rows. > > I don't have a Windows box handy on which I can test Excel's behaviour. > Clearly my experience is out of date - I just found a web ref to Excel doing just this. Oh, well, it doesn't seem to me an insurmountable problem. cheers andrew
Karel Zak <zakkr@zf.jcu.cz> writes:
> And what \n in attibutes data in CSV? I think CSV format doesn't use
> some escape for newline char. It means psql with \copy cannot be sure
> with CSV.
I think CSV only allows embedded newlines that are either escaped, or
inside quotes. COPY doesn't currently have the notion of a quote
character, but that was part of Andrew's proposal ...
regards, tom lane
<quote who="Andrew Dunstan"> > > > Karel Zak wrote: > >> The problem with CSV is that it will correctly work with new protocol >> only. Because old versions of clients are newline sensitive. And CSV >> can contains newline in by quotation marks defined attributes: >> >> "John", "Smith", "The White House >> 1600 Pennsylvania Avenue NW >> Washington, DC 20500", "male", "open source software office" >> >> It is one record. >> > > (Long Live President Smith!) > > I have never seen such a beast, and if I had been asked off the cuff > would have said that it was probably illegal, except that I know of no > standard actually defining the format. Perhaps others (Joshua Drake or > Josh Berkus?) have wider experience. I think in any case we should > ignore those for now and handle the straightforward case. > > I *have* seen monstrosities like fields that do not begin with the quote > character but then break into a quote, e.g.: > > 1,2,a,123"abc""def",6,7,8 > I have dealt with both, frequently. The CSV format allows you to begin a block of text with the quote. Newlines are included in that quoted space. If qoutes are included in the field then the quotesare double quotes to denote they are not part of the quoted space. Also, the following is valid. 1,2,,,"",,,"" "" is empty. 1,2,3,"""",,,,"", The 4 quotes denote a single double quote. Writing simple CSV converts that just explode on commas and newlines miss these oddities. Try exporting an Access table with a Memo field (containg multiple lines) to CSV.
Tom Lane wrote: >Andrew Dunstan <andrew@dunslane.net> writes: > > >>There are some wrinkles, though, concerning the interaction of CSV's >>notion of escaping and COPY's notion of escaping. If someone want to >>undertake this I can flesh those out in a further email. >> >> > >Please do that, so that the info is in the archives in case someone else >wants to tackle the project. > > > briefly: According to my understanding, in a CSV file backslash has no magical meaning unless it is the escape character, in which case we only expect to find it prefacing either itself or the quote character inside a quoted field. Otherwise, it is just another character. One way of handling this might be to have 2 modes of COPY processing: . if a quote delimiter is specified turn off all of COPY's usual backslash processing, and make the default NULL marker the empty string . if no quote delimiter is specified, act as now. OTOH it might be a good idea to be able to turn off backslash processing even without a quote delimiter, e.g. in a CSV-like file using tab as the delimiter and no quote escaping, so maybe another switch on COPY would be a better way to go. Another issue I wondered about is how to specify nicely that TAB is the field delimiter - I hate putting a literal semantic tab in files, and consider its magical use in places like Makefiles and syslog.conf files some of the worst decisions in computing ever made ;-). I'd like a nicer *visible* way of specifying it, either with \t or ^I maybe. I'm sure other issues will arise - that's all that's in my head for the moment :-) cheers andrew
Thomas Swan wrote: > > I have never seen such a beast, and if I had been asked off the cuff > > would have said that it was probably illegal, except that I know of no > > standard actually defining the format. Perhaps others (Joshua Drake or > > Josh Berkus?) have wider experience. I think in any case we should > > ignore those for now and handle the straightforward case. > > > > I *have* seen monstrosities like fields that do not begin with the quote > > character but then break into a quote, e.g.: > > > > 1,2,a,123"abc""def",6,7,8 > > > > I have dealt with both, frequently. The CSV format allows you to begin a > block of text with the quote. Newlines are included in that quoted space. > If qoutes are included in the field then the quotes are double quotes to > denote they are not part of the quoted space. > > Also, the following is valid. > > 1,2,,,"",,,"" > > "" is empty. > > 1,2,3,"""",,,,"", > > The 4 quotes denote a single double quote. > > Writing simple CSV converts that just explode on commas and newlines miss > these oddities. OK, so for the separator, quote, and escape options: separator is ,quote is "escape is " so if the quote and escape are the same, then a double denotes a single? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: >OK, so for the separator, quote, and escape options: > > separator is , > quote is " > escape is " > >so if the quote and escape are the same, then a double denotes a >single? > > > Yes. i.e. with the above settings "abc""def" -> abc"def cheers andrew
On Fri, Mar 19, 2004 at 09:54:37AM -0500, Tom Lane wrote: > Karel Zak <zakkr@zf.jcu.cz> writes: > > The problem with CSV is that it will correctly work with new protocol > > only. Because old versions of clients are newline sensitive. > > Why? The client-side code doesn't have any real say over the meaning of > the data, at least not in psql-class clients. I suppose a client app > that tries to interpret the data could get confused, but psql sure > doesn't do that. libpq, fe-protocol2.c, pqGetCopyData2() and pqGetline2() cut stringbehind '\n'. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
Karel Zak <zakkr@zf.jcu.cz> writes:
> On Fri, Mar 19, 2004 at 09:54:37AM -0500, Tom Lane wrote:
>> Why? The client-side code doesn't have any real say over the meaning of
>> the data, at least not in psql-class clients. I suppose a client app
>> that tries to interpret the data could get confused, but psql sure
>> doesn't do that.
> libpq, fe-protocol2.c, pqGetCopyData2() and pqGetline2() cut string
> behind '\n'.
Sure, but that doesn't invalidate the data stream as a whole, it's just
a bufferload boundary choice that won't be very helpful for clients not
using a newline-based data layout. In any case, v2 protocol is
obsolete and needn't limit our thoughts about what to do in future.
regards, tom lane