Thread: COPY for CSVs
Attached is an undocumented patch that implements COPY for CSVs
according to the scheme recently discussed. I believe it handles all the
straightforward and perverse cases that were discussed (including
multiline fields and partially quoted fields).
I have done some light testing on it, but would appreciate some
assistance with testing, as well as any useful review comments.
cheers
andrew
Index: src/backend/commands/copy.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/copy.c,v
retrieving revision 1.219
diff -c -r1.219 copy.c
*** src/backend/commands/copy.c 6 Apr 2004 13:21:33 -0000 1.219
--- src/backend/commands/copy.c 8 Apr 2004 18:50:38 -0000
***************
*** 70,76 ****
typedef enum CopyReadResult
{
NORMAL_ATTR,
! END_OF_LINE
} CopyReadResult;
/*
--- 70,77 ----
typedef enum CopyReadResult
{
NORMAL_ATTR,
! END_OF_LINE,
! UNTERMINATED_FIELD
} CopyReadResult;
/*
***************
*** 136,141 ****
--- 137,144 ----
static bool CopyReadLine(void);
static char *CopyReadAttribute(const char *delim, const char *null_print,
CopyReadResult *result, bool *isnull);
+ static char *CopyReadAttributeCSV(const char *delim, const char *null_print,
+ CopyReadResult *result, bool *isnull);
static Datum CopyReadBinaryAttribute(int column_no, FmgrInfo *flinfo,
Oid typelem, bool *isnull);
static void CopyAttributeOut(char *string, char *delim);
***************
*** 682,687 ****
--- 685,691 ----
List *attnumlist;
bool binary = false;
bool oids = false;
+ bool csv_mode = false;
char *delim = NULL;
char *null_print = NULL;
Relation rel;
***************
*** 744,751 ****
if (!delim)
delim = "\t";
if (!null_print)
! null_print = "\\N";
/*
* Open and lock the relation, using the appropriate lock type.
--- 748,758 ----
if (!delim)
delim = "\t";
+ if (is_from && strlen(delim) > 1)
+ csv_mode = true;
+
if (!null_print)
! null_print = csv_mode ? "" : "\\N";
/*
* Open and lock the relation, using the appropriate lock type.
***************
*** 772,783 ****
"psql's \\copy command also works for anyone.")));
/*
! * Presently, only single-character delimiter strings are supported.
*/
! if (strlen(delim) != 1)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY delimiter must be a single character")));
/*
* Don't allow the delimiter to appear in the null string.
--- 779,805 ----
"psql's \\copy command also works for anyone.")));
/*
! * Only single-character delimiter strings are supported,
! * except in CSV mode, where the string must be
! * delimiter-char quote-char [escape-char]
*/
! if (!csv_mode && strlen(delim) != 1)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY delimiter must be a single character")));
+ else if (csv_mode)
+ {
+ if(strlen(delim) > 3)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("COPY delimiters for CSV must be a 2 or 3 characters")));
+ if (delim[0] == delim[1] ||
+ (strlen(delim) == 3 && delim[0] == delim[2]))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("CSV delimiter character must not be same as quote character or escape character")));
+
+ }
/*
* Don't allow the delimiter to appear in the null string.
***************
*** 788,793 ****
--- 810,824 ----
errmsg("COPY delimiter must not appear in the NULL specification")));
/*
+ * Don't allow OIDs in CSV mode
+ */
+
+ if (csv_mode && oids)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("Cannot specify OIDS in CSV mode ")));
+
+ /*
* Don't allow COPY w/ OIDs to or from a table without them
*/
if (oids && !rel->rd_rel->relhasoids)
***************
*** 1263,1268 ****
--- 1294,1300 ----
Datum *values;
char *nulls;
bool done = false;
+ bool csv_mode;
bool isnull;
ResultRelInfo *resultRelInfo;
EState *estate = CreateExecutorState(); /* for ExecConstraints() */
***************
*** 1280,1285 ****
--- 1312,1318 ----
num_phys_attrs = tupDesc->natts;
attr_count = length(attnumlist);
num_defaults = 0;
+ csv_mode = (strlen(delim) > 1);
/*
* We need a ResultRelInfo so we can use the regular executor's
***************
*** 1499,1504 ****
--- 1532,1538 ----
if (file_has_oids)
{
+ /* can't be in CSV mode here */
string = CopyReadAttribute(delim, null_print,
&result, &isnull);
***************
*** 1537,1544 ****
errmsg("missing data for column \"%s\"",
NameStr(attr[m]->attname))));
! string = CopyReadAttribute(delim, null_print,
! &result, &isnull);
if (isnull)
{
--- 1571,1591 ----
errmsg("missing data for column \"%s\"",
NameStr(attr[m]->attname))));
! if (csv_mode)
! {
! string = CopyReadAttributeCSV(delim, null_print,
! &result, &isnull);
! if (result == UNTERMINATED_FIELD)
! ereport(ERROR,
! (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
! errmsg("unterminated CSV quoted field")));
! }
! else
! {
! string = CopyReadAttribute(delim, null_print,
! &result, &isnull);
! }
!
if (isnull)
{
***************
*** 2061,2066 ****
--- 2108,2263 ----
/* check whether raw input matched null marker */
input_len = end_cursor - start_cursor;
if (input_len == strlen(null_print) &&
+ strncmp(&line_buf.data[start_cursor], null_print, input_len) == 0)
+ *isnull = true;
+ else
+ *isnull = false;
+
+ return attribute_buf.data;
+ }
+
+
+ /*
+ * Read the value of a single attribute in CSV mode,
+ * performing de-escaping as needed. Escaping does not follow the normal
+ * PostgreSQL text mode, but instead "standard" (i.e. common) CSV usage.
+ *
+ * Quoted fields can span lines, in which case the line end is embedded
+ * in the returned string.
+ *
+ * delim is a 2- or 3-character string. The first character is the
+ * field delimiter, the second the quote character, the third is the
+ * escape character indise quotes, and defaults to the quote character.
+ *
+ * null_print is the null marker string. Note that this is compared to
+ * the pre-de-escaped input string (thus if it is quoted it is not a NULL).
+ *
+ * *result is set to indicate what terminated the read:
+ * NORMAL_ATTR: column delimiter
+ * END_OF_LINE: end of line
+ * UNTERMINATED_FIELD no quote detected at end of a quoted field
+ *
+ * In any case, the string read up to the terminator (or end of file)
+ * is returned.
+ *
+ * *isnull is set true or false depending on whether the input matched
+ * the null marker. Note that the caller cannot check this since the
+ * returned string will be the post-de-escaping equivalent, which may
+ * look the same as some valid data string.
+ *----------
+ */
+
+ static char *
+ CopyReadAttributeCSV(const char *delim, const char *null_print,
+ CopyReadResult *result, bool *isnull)
+ {
+ char delimc = delim[0];
+ char quotec = delim[1];
+ char escapec = delim[2] ? delim[2] : delim[1];
+ char c;
+ int start_cursor = line_buf.cursor;
+ int end_cursor = start_cursor;;
+ int input_len;
+ bool in_quote = false;
+ bool saw_quote = false;
+
+ /* reset attribute_buf to empty */
+ attribute_buf.len = 0;
+ attribute_buf.data[0] = '\0';
+
+ /* set default status */
+ *result = END_OF_LINE;
+
+ for (;;)
+ {
+ /* handle multiline quoted fields */
+ if (in_quote && line_buf.cursor >= line_buf.len)
+ {
+ bool done;
+
+ switch(eol_type)
+ {
+ case EOL_NL:
+ appendStringInfoString(&attribute_buf,"\n");
+ break;
+ case EOL_CR:
+ appendStringInfoString(&attribute_buf,"\r");
+ break;
+ case EOL_CRNL:
+ appendStringInfoString(&attribute_buf,"\r\n");
+ break;
+ case EOL_UNKNOWN:
+ /* shouldn't happen - just keep going */
+ break;
+ }
+
+ copy_lineno++;
+ done = CopyReadLine();
+ if (done && line_buf.len == 0)
+ break;
+ start_cursor = line_buf.cursor;
+ }
+
+ end_cursor = line_buf.cursor;
+ if (line_buf.cursor >= line_buf.len)
+ break;
+ c = line_buf.data[line_buf.cursor++];
+ /*
+ * unquoted field delimiter
+ */
+ if (!in_quote && c == delimc)
+ {
+ *result = NORMAL_ATTR;
+ break;
+ }
+ /*
+ * start of quoted field (or part of field)
+ */
+ if (!in_quote && c == quotec)
+ {
+ saw_quote = true;
+ in_quote = true;
+ continue;
+ }
+ /*
+ * escape within a quoted field
+ */
+ if (in_quote && c == escapec)
+ {
+ /*
+ * peek at the next char if available, and escape it if it
+ * is an escape char or a quote char
+ */
+ if (line_buf.cursor <= line_buf.len)
+ {
+ char nextc = line_buf.data[line_buf.cursor];
+ if (nextc == escapec || nextc == quotec)
+ {
+ appendStringInfoCharMacro(&attribute_buf, nextc);
+ line_buf.cursor++;
+ continue;
+ }
+ }
+ }
+ /*
+ * end of quoted field.
+ * Must do this test after testing for escape in case quote char
+ * and escape char are the same (which is the common case).
+ */
+ if(in_quote && c == quotec)
+ {
+ in_quote = false;
+ continue;
+ }
+ appendStringInfoCharMacro(&attribute_buf, c);
+ }
+
+ if (in_quote)
+ *result = UNTERMINATED_FIELD;
+
+ /* check whether raw input matched null marker */
+ input_len = end_cursor - start_cursor;
+ if (!saw_quote && input_len == strlen(null_print) &&
strncmp(&line_buf.data[start_cursor], null_print, input_len) == 0)
*isnull = true;
else
Andrew Dunstan wrote: > > Attached is an undocumented patch that implements COPY for CSVs > according to the scheme recently discussed. I believe it handles all > the straightforward and perverse cases that were discussed (including > multiline fields and partially quoted fields). > > I have done some light testing on it, but would appreciate some > assistance with testing, as well as any useful review comments. > Looking back over the discussion, I see that there was demand for the ability to export as well as import CSVs. That will actually be rather easier to do, so I will revise the patch accordingly. cheers andrew
Andrew Dunstan wrote:
> Andrew Dunstan wrote:
>
>>
>> Attached is an undocumented patch that implements COPY for CSVs
>> according to the scheme recently discussed. I believe it handles all
>> the straightforward and perverse cases that were discussed (including
>> multiline fields and partially quoted fields).
>>
>> I have done some light testing on it, but would appreciate some
>> assistance with testing, as well as any useful review comments.
>>
>
> Looking back over the discussion, I see that there was demand for the
> ability to export as well as import CSVs.
>
> That will actually be rather easier to do, so I will revise the patch
> accordingly.
>
version attached that does copy out as well as copy in. I am not sure
about the multibyte stuff, so that needs careful review.
cheers
andrew
Index: src/backend/commands/copy.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/copy.c,v
retrieving revision 1.219
diff -c -r1.219 copy.c
*** src/backend/commands/copy.c 6 Apr 2004 13:21:33 -0000 1.219
--- src/backend/commands/copy.c 9 Apr 2004 23:47:53 -0000
***************
*** 70,76 ****
typedef enum CopyReadResult
{
NORMAL_ATTR,
! END_OF_LINE
} CopyReadResult;
/*
--- 70,77 ----
typedef enum CopyReadResult
{
NORMAL_ATTR,
! END_OF_LINE,
! UNTERMINATED_FIELD
} CopyReadResult;
/*
***************
*** 136,144 ****
--- 137,148 ----
static bool CopyReadLine(void);
static char *CopyReadAttribute(const char *delim, const char *null_print,
CopyReadResult *result, bool *isnull);
+ static char *CopyReadAttributeCSV(const char *delim, const char *null_print,
+ CopyReadResult *result, bool *isnull);
static Datum CopyReadBinaryAttribute(int column_no, FmgrInfo *flinfo,
Oid typelem, bool *isnull);
static void CopyAttributeOut(char *string, char *delim);
+ static void CopyAttributeOutCSV(char *string, char *delim);
static List *CopyGetAttnums(Relation rel, List *attnamelist);
static void limit_printout_length(StringInfo buf);
***************
*** 682,687 ****
--- 686,692 ----
List *attnumlist;
bool binary = false;
bool oids = false;
+ bool csv_mode = false;
char *delim = NULL;
char *null_print = NULL;
Relation rel;
***************
*** 744,751 ****
if (!delim)
delim = "\t";
if (!null_print)
! null_print = "\\N";
/*
* Open and lock the relation, using the appropriate lock type.
--- 749,759 ----
if (!delim)
delim = "\t";
+ if (strlen(delim) > 1)
+ csv_mode = true;
+
if (!null_print)
! null_print = csv_mode ? "" : "\\N";
/*
* Open and lock the relation, using the appropriate lock type.
***************
*** 772,783 ****
"psql's \\copy command also works for anyone.")));
/*
! * Presently, only single-character delimiter strings are supported.
*/
! if (strlen(delim) != 1)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY delimiter must be a single character")));
/*
* Don't allow the delimiter to appear in the null string.
--- 780,806 ----
"psql's \\copy command also works for anyone.")));
/*
! * Only single-character delimiter strings are supported,
! * except in CSV mode, where the string must be
! * delimiter-char quote-char [escape-char]
*/
! if (!csv_mode && strlen(delim) != 1)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY delimiter must be a single character")));
+ else if (csv_mode)
+ {
+ if(strlen(delim) > 3)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("COPY delimiters for CSV must be a 2 or 3 characters")));
+ if (delim[0] == delim[1] ||
+ (strlen(delim) == 3 && delim[0] == delim[2]))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("CSV delimiter character must not be same as quote character or escape character")));
+
+ }
/*
* Don't allow the delimiter to appear in the null string.
***************
*** 788,793 ****
--- 811,825 ----
errmsg("COPY delimiter must not appear in the NULL specification")));
/*
+ * Don't allow OIDs in CSV mode
+ */
+
+ if (csv_mode && oids)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("Cannot specify OIDS in CSV mode ")));
+
+ /*
* Don't allow COPY w/ OIDs to or from a table without them
*/
if (oids && !rel->rd_rel->relhasoids)
***************
*** 969,974 ****
--- 1001,1007 ----
FmgrInfo *out_functions;
Oid *elements;
bool *isvarlena;
+ bool csv_mode;
char *string;
Snapshot mySnapshot;
List *cur;
***************
*** 979,984 ****
--- 1012,1018 ----
attr = tupDesc->attrs;
num_phys_attrs = tupDesc->natts;
attr_count = length(attnumlist);
+ csv_mode = (strlen(delim) > 1);
/*
* Get info about the columns we need to process.
***************
*** 1051,1057 ****
while ((tuple = heap_getnext(scandesc, ForwardScanDirection)) != NULL)
{
bool need_delim = false;
-
CHECK_FOR_INTERRUPTS();
MemoryContextReset(mycontext);
--- 1085,1090 ----
***************
*** 1113,1119 ****
value,
ObjectIdGetDatum(elements[attnum - 1]),
Int32GetDatum(attr[attnum - 1]->atttypmod)));
! CopyAttributeOut(string, delim);
}
else
{
--- 1146,1156 ----
value,
ObjectIdGetDatum(elements[attnum - 1]),
Int32GetDatum(attr[attnum - 1]->atttypmod)));
! if (csv_mode)
! CopyAttributeOutCSV(string, delim);
! else
! CopyAttributeOut(string, delim);
!
}
else
{
***************
*** 1263,1268 ****
--- 1300,1306 ----
Datum *values;
char *nulls;
bool done = false;
+ bool csv_mode;
bool isnull;
ResultRelInfo *resultRelInfo;
EState *estate = CreateExecutorState(); /* for ExecConstraints() */
***************
*** 1280,1285 ****
--- 1318,1324 ----
num_phys_attrs = tupDesc->natts;
attr_count = length(attnumlist);
num_defaults = 0;
+ csv_mode = (strlen(delim) > 1);
/*
* We need a ResultRelInfo so we can use the regular executor's
***************
*** 1499,1504 ****
--- 1538,1544 ----
if (file_has_oids)
{
+ /* can't be in CSV mode here */
string = CopyReadAttribute(delim, null_print,
&result, &isnull);
***************
*** 1537,1544 ****
errmsg("missing data for column \"%s\"",
NameStr(attr[m]->attname))));
! string = CopyReadAttribute(delim, null_print,
! &result, &isnull);
if (isnull)
{
--- 1577,1597 ----
errmsg("missing data for column \"%s\"",
NameStr(attr[m]->attname))));
! if (csv_mode)
! {
! string = CopyReadAttributeCSV(delim, null_print,
! &result, &isnull);
! if (result == UNTERMINATED_FIELD)
! ereport(ERROR,
! (errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
! errmsg("unterminated CSV quoted field")));
! }
! else
! {
! string = CopyReadAttribute(delim, null_print,
! &result, &isnull);
! }
!
if (isnull)
{
***************
*** 2069,2074 ****
--- 2122,2277 ----
return attribute_buf.data;
}
+
+ /*
+ * Read the value of a single attribute in CSV mode,
+ * performing de-escaping as needed. Escaping does not follow the normal
+ * PostgreSQL text mode, but instead "standard" (i.e. common) CSV usage.
+ *
+ * Quoted fields can span lines, in which case the line end is embedded
+ * in the returned string.
+ *
+ * delim is a 2- or 3-character string. The first character is the
+ * field delimiter, the second the quote character, the third is the
+ * escape character indise quotes, and defaults to the quote character.
+ *
+ * null_print is the null marker string. Note that this is compared to
+ * the pre-de-escaped input string (thus if it is quoted it is not a NULL).
+ *
+ * *result is set to indicate what terminated the read:
+ * NORMAL_ATTR: column delimiter
+ * END_OF_LINE: end of line
+ * UNTERMINATED_FIELD no quote detected at end of a quoted field
+ *
+ * In any case, the string read up to the terminator (or end of file)
+ * is returned.
+ *
+ * *isnull is set true or false depending on whether the input matched
+ * the null marker. Note that the caller cannot check this since the
+ * returned string will be the post-de-escaping equivalent, which may
+ * look the same as some valid data string.
+ *----------
+ */
+
+ static char *
+ CopyReadAttributeCSV(const char *delim, const char *null_print,
+ CopyReadResult *result, bool *isnull)
+ {
+ char delimc = delim[0];
+ char quotec = delim[1];
+ char escapec = delim[2] ? delim[2] : delim[1];
+ char c;
+ int start_cursor = line_buf.cursor;
+ int end_cursor = start_cursor;;
+ int input_len;
+ bool in_quote = false;
+ bool saw_quote = false;
+
+ /* reset attribute_buf to empty */
+ attribute_buf.len = 0;
+ attribute_buf.data[0] = '\0';
+
+ /* set default status */
+ *result = END_OF_LINE;
+
+ for (;;)
+ {
+ /* handle multiline quoted fields */
+ if (in_quote && line_buf.cursor >= line_buf.len)
+ {
+ bool done;
+
+ switch(eol_type)
+ {
+ case EOL_NL:
+ appendStringInfoString(&attribute_buf,"\n");
+ break;
+ case EOL_CR:
+ appendStringInfoString(&attribute_buf,"\r");
+ break;
+ case EOL_CRNL:
+ appendStringInfoString(&attribute_buf,"\r\n");
+ break;
+ case EOL_UNKNOWN:
+ /* shouldn't happen - just keep going */
+ break;
+ }
+
+ copy_lineno++;
+ done = CopyReadLine();
+ if (done && line_buf.len == 0)
+ break;
+ start_cursor = line_buf.cursor;
+ }
+
+ end_cursor = line_buf.cursor;
+ if (line_buf.cursor >= line_buf.len)
+ break;
+ c = line_buf.data[line_buf.cursor++];
+ /*
+ * unquoted field delimiter
+ */
+ if (!in_quote && c == delimc)
+ {
+ *result = NORMAL_ATTR;
+ break;
+ }
+ /*
+ * start of quoted field (or part of field)
+ */
+ if (!in_quote && c == quotec)
+ {
+ saw_quote = true;
+ in_quote = true;
+ continue;
+ }
+ /*
+ * escape within a quoted field
+ */
+ if (in_quote && c == escapec)
+ {
+ /*
+ * peek at the next char if available, and escape it if it
+ * is an escape char or a quote char
+ */
+ if (line_buf.cursor <= line_buf.len)
+ {
+ char nextc = line_buf.data[line_buf.cursor];
+ if (nextc == escapec || nextc == quotec)
+ {
+ appendStringInfoCharMacro(&attribute_buf, nextc);
+ line_buf.cursor++;
+ continue;
+ }
+ }
+ }
+ /*
+ * end of quoted field.
+ * Must do this test after testing for escape in case quote char
+ * and escape char are the same (which is the common case).
+ */
+ if(in_quote && c == quotec)
+ {
+ in_quote = false;
+ continue;
+ }
+ appendStringInfoCharMacro(&attribute_buf, c);
+ }
+
+ if (in_quote)
+ *result = UNTERMINATED_FIELD;
+
+ /* check whether raw input matched null marker */
+ input_len = end_cursor - start_cursor;
+ if (!saw_quote && input_len == strlen(null_print) &&
+ strncmp(&line_buf.data[start_cursor], null_print, input_len) == 0)
+ *isnull = true;
+ else
+ *isnull = false;
+
+ return attribute_buf.data;
+ }
+
/*
* Read a binary attribute
*/
***************
*** 2192,2197 ****
--- 2395,2467 ----
break;
}
}
+ }
+
+ /*
+ * Send CSV representation of one attribute, with conversion and
+ * CSV type escaping
+ */
+ static void
+ CopyAttributeOutCSV(char *server_string, char *delim)
+ {
+ char *string;
+ char c;
+ char delimc = delim[0];
+ char quotec = delim[1];
+ char escapec = delim[2] ? delim[2] : delim[1];
+ bool need_quote = false;
+ char *test_string;
+ bool same_encoding;
+ int mblen;
+ int i;
+
+ same_encoding = (server_encoding == client_encoding);
+ if (!same_encoding)
+ string = (char *) pg_server_to_client((unsigned char *) server_string,
+ strlen(server_string));
+ else
+ string = server_string;
+
+ /* have to run through the string twice,
+ * first time to see if it needs quoting, second to actually send it
+ */
+
+ for(test_string = string; (c = *test_string) != '\0'; test_string += mblen)
+ {
+ if (c == delimc || c == quotec || c == '\n' || c == '\r')
+ {
+ need_quote = true;
+ break;
+ }
+ if (!same_encoding)
+ mblen = pg_encoding_mblen(client_encoding, test_string);
+ else
+ mblen = 1;
+ }
+
+ if (need_quote)
+ CopySendChar(quotec);
+
+ for (; (c = *string) != '\0'; string += mblen)
+ {
+ if (c == quotec || c == escapec)
+ CopySendChar(escapec);
+
+ CopySendChar(c);
+
+ if (!same_encoding)
+ {
+ /* send additional bytes of the char, if any */
+ mblen = pg_encoding_mblen(client_encoding, string);
+ for (i = 1; i < mblen; i++)
+ CopySendChar(string[i]);
+ }
+ else
+ mblen = 1;
+ }
+
+ if (need_quote)
+ CopySendChar(quotec);
}
/*