Hi here is a version with both direction support.
postgres=# copy foo from '/tmp/1.jpg' (format raw); COPY 1 Time: 93.021 ms postgres=# \dt+ foo List of relations ┌────────┬──────┬───────┬───────┬────────┬─────────────┐ │ Schema │ Name │ Type │ Owner │ Size │ Description │ ╞════════╪══════╪═══════╪═══════╪════════╪═════════════╡ │ public │ foo │ table │ pavel │ 256 kB │ │ └────────┴──────┴───────┴───────┴────────┴─────────────┘ (1 row) postgres=# \copy foo to '~/3.jpg' (format raw) COPY 1 Time: 2.401 ms Regards Pavel 2015-07-02 17:02 GMT+02:00 Tom Lane <t...@sss.pgh.pa.us>: > Andrew Dunstan <and...@dunslane.net> writes: > > Does the COPY line protocol even support binary data? > > The protocol, per se, just transmits a byte stream. There is a field > in the CopyInResponse/CopyOutResponse messages that indicates whether > a text or binary copy is being done. One thing we'd have to consider > is whether "raw" mode is sufficiently different from binary to justify > an additional value for this field, and if so whether that constitutes > a protocol break. > > IIRC, psql wouldn't really care; it just transfers the byte stream to or > from the target file, regardless of text or binary mode. But there might > be other client libraries that are smarter and expect "binary" mode to > mean the binary file format specified in the COPY reference page. So > there may be value in being explicit about "raw" mode in these messages. > > A key point in all this is that people who need "raw" transfer probably > need it in both directions, a point that your SELECT proposal cannot > satisfy, but hacking COPY could. So I lean towards the latter really. > > regards, tom lane >
commit 5599347d6b0b29a2674d465b3ff03164fce59810 Author: Pavel Stehule <pavel.steh...@gooddata.com> Date: Mon Jul 6 23:18:18 2015 +0200 COPY FROM/TO (FORMAT RAW) diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 2850b47..4b7b64d 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -190,7 +190,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable Selects the data format to be read or written: <literal>text</>, <literal>csv</> (Comma Separated Values), - or <literal>binary</>. + <literal>binary</> or <literal>raw</literal>. The default is <literal>text</>. </para> </listitem> @@ -881,6 +881,23 @@ OIDs to be shown as null if that ever proves desirable. </para> </refsect3> </refsect2> + + <refsect2> + <title>Raw Format</title> + + <para> + The <literal>raw</literal> format option causes all data to be + stored/read as binary format rather than as text. It shares format + for data with <literal>binary</literal> format. This format doesn't + use any metadata - only row data in network byte order are exported + or imported. + </para> + + <para> + Because this format doesn't support any delimiter, only one value + can be exported or imported. NULL values are not allowed. + </para> + </refsect2> </refsect1> <refsect1> diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 8904676..2ad7eb1 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -92,6 +92,11 @@ typedef enum EolType * it's faster to make useless comparisons to trailing bytes than it is to * invoke pg_encoding_mblen() to skip over them. encoding_embeds_ascii is TRUE * when we have to do it the hard way. + * + * COPY supports three modes: text, binary and raw. The text format is plain + * text multiline format with specified delimiter. The binary format holds + * metadata (numbers, sizes) and data. The raw format holds data only and + * only one non NULL value can be processed. */ typedef struct CopyStateData { @@ -113,6 +118,7 @@ typedef struct CopyStateData char *filename; /* filename, or NULL for STDIN/STDOUT */ bool is_program; /* is 'filename' a program to popen? */ bool binary; /* binary format? */ + bool raw; /* required raw binary? */ bool oids; /* include OIDs? */ bool freeze; /* freeze rows on loading? */ bool csv_mode; /* Comma Separated Value format? */ @@ -202,6 +208,9 @@ typedef struct CopyStateData char *raw_buf; int raw_buf_index; /* next byte to process */ int raw_buf_len; /* total # of bytes stored */ + + /* field for RAW mode */ + bool row_processed; /* true, when first row was processed */ } CopyStateData; /* DestReceiver for COPY (SELECT) TO */ @@ -345,9 +354,16 @@ SendCopyBegin(CopyState cstate) /* new way */ StringInfoData buf; int natts = list_length(cstate->attnumlist); - int16 format = (cstate->binary ? 1 : 0); + int16 format; int i; + if (cstate->raw) + format = 2; + else if (cstate->binary) + format = 1; + else + format = 0; + pq_beginmessage(&buf, 'H'); pq_sendbyte(&buf, format); /* overall format */ pq_sendint(&buf, natts, 2); @@ -359,7 +375,7 @@ SendCopyBegin(CopyState cstate) else if (PG_PROTOCOL_MAJOR(FrontendProtocol) >= 2) { /* old way */ - if (cstate->binary) + if (cstate->binary && cstate->raw) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("COPY BINARY is not supported to stdout or from stdin"))); @@ -371,7 +387,7 @@ SendCopyBegin(CopyState cstate) else { /* very old way */ - if (cstate->binary) + if (cstate->binary && cstate->raw) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("COPY BINARY is not supported to stdout or from stdin"))); @@ -390,9 +406,16 @@ ReceiveCopyBegin(CopyState cstate) /* new way */ StringInfoData buf; int natts = list_length(cstate->attnumlist); - int16 format = (cstate->binary ? 1 : 0); + int16 format; int i; + if (cstate->raw) + format = 2; + else if (cstate->binary) + format = 1; + else + format = 0; + pq_beginmessage(&buf, 'G'); pq_sendbyte(&buf, format); /* overall format */ pq_sendint(&buf, natts, 2); @@ -405,7 +428,7 @@ ReceiveCopyBegin(CopyState cstate) else if (PG_PROTOCOL_MAJOR(FrontendProtocol) >= 2) { /* old way */ - if (cstate->binary) + if (cstate->binary || cstate->raw) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("COPY BINARY is not supported to stdout or from stdin"))); @@ -417,7 +440,7 @@ ReceiveCopyBegin(CopyState cstate) else { /* very old way */ - if (cstate->binary) + if (cstate->binary || cstate->raw) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("COPY BINARY is not supported to stdout or from stdin"))); @@ -485,7 +508,7 @@ CopySendEndOfRow(CopyState cstate) switch (cstate->copy_dest) { case COPY_FILE: - if (!cstate->binary) + if (!cstate->binary && !cstate->raw) { /* Default line termination depends on platform */ #ifndef WIN32 @@ -530,7 +553,7 @@ CopySendEndOfRow(CopyState cstate) break; case COPY_OLD_FE: /* The FE/BE protocol uses \n as newline for all platforms */ - if (!cstate->binary) + if (!cstate->binary && !cstate->raw) CopySendChar(cstate, '\n'); if (pq_putbytes(fe_msgbuf->data, fe_msgbuf->len)) @@ -543,7 +566,7 @@ CopySendEndOfRow(CopyState cstate) break; case COPY_NEW_FE: /* The FE/BE protocol uses \n as newline for all platforms */ - if (!cstate->binary) + if (!cstate->binary && !cstate->raw) CopySendChar(cstate, '\n'); /* Dump the accumulated row as one CopyData message */ @@ -600,6 +623,7 @@ CopyGetData(CopyState cstate, void *databuf, int minread, int maxread) bytesread = minread; break; case COPY_NEW_FE: + while (maxread > 0 && bytesread < minread && !cstate->fe_eof) { int avail; @@ -769,6 +793,36 @@ CopyLoadRawBuf(CopyState cstate) return (inbytes > 0); } +/* + * CopyLoadAllRawBuf load all file into raw_buf. + * + * It is used for reading content in raw mode. If original RAW_BUF_SIZE is not + * enough, the buffer is enlarged. + */ +static void +CopyLoadAllRawBuf(CopyState cstate) +{ + int nbytes = 0; + int inbytes; + Size raw_buf_size = RAW_BUF_SIZE; + + inbytes = CopyGetData(cstate, cstate->raw_buf + nbytes, 1, RAW_BUF_SIZE); + while (inbytes == RAW_BUF_SIZE) + { + nbytes += inbytes; + + /* Have to enlarge raw_buf */ + raw_buf_size += RAW_BUF_SIZE + 1; + cstate->raw_buf = repalloc(cstate->raw_buf, raw_buf_size); + + inbytes = CopyGetData(cstate, cstate->raw_buf + nbytes, 1, RAW_BUF_SIZE); + } + + nbytes += inbytes; + cstate->raw_buf[nbytes] = '\0'; + cstate->raw_buf_index = 0; + cstate->raw_buf_len = nbytes; +} /* * DoCopy executes the SQL COPY statement @@ -1006,6 +1060,8 @@ ProcessCopyOptions(CopyState cstate, cstate->csv_mode = true; else if (strcmp(fmt, "binary") == 0) cstate->binary = true; + else if (strcmp(fmt, "raw") == 0) + cstate->raw = true; else ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), @@ -1155,15 +1211,20 @@ ProcessCopyOptions(CopyState cstate, * Check for incompatible options (must do these two before inserting * defaults) */ - if (cstate->binary && cstate->delim) + if ((cstate->binary || cstate->raw) && cstate->delim) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("cannot specify DELIMITER in BINARY or RAW mode"))); + + if ((cstate->binary || cstate->raw) && cstate->null_print) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("cannot specify DELIMITER in BINARY mode"))); + errmsg("cannot specify NULL in BINARY or RAW mode"))); - if (cstate->binary && cstate->null_print) + if (cstate->raw && cstate->oids) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("cannot specify NULL in BINARY mode"))); + errmsg("cannot specify OIDS in RAW mode"))); /* Set defaults for omitted options */ if (!cstate->delim) @@ -1559,6 +1620,20 @@ BeginCopy(bool is_from, } } + /* + * Initializaze the field "row_processed" for one row output in RAW mode, + * and ensure only one output column. + */ + if (cstate->raw) + { + cstate->row_processed = false; + + if (num_phys_attrs > 1) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("only single column result is allowed in RAW mode"))); + } + /* Use client encoding when ENCODING option is not specified. */ if (cstate->file_encoding < 0) cstate->file_encoding = pg_get_client_encoding(); @@ -1821,7 +1896,7 @@ CopyTo(CopyState cstate) Oid out_func_oid; bool isvarlena; - if (cstate->binary) + if (cstate->binary || cstate->raw) getTypeBinaryOutputInfo(attr[attnum - 1]->atttypid, &out_func_oid, &isvarlena); @@ -1860,7 +1935,7 @@ CopyTo(CopyState cstate) tmp = 0; CopySendInt32(cstate, tmp); } - else + else if (!cstate->raw) { /* * For non-binary copy, we need to convert null_print to file @@ -1928,7 +2003,7 @@ CopyTo(CopyState cstate) else { /* run the plan --- the dest receiver will send tuples */ - ExecutorRun(cstate->queryDesc, ForwardScanDirection, 0L); + ExecutorRun(cstate->queryDesc, ForwardScanDirection, cstate->raw ? 2L : 0L); processed = ((DR_copy *) cstate->queryDesc->dest)->processed; } @@ -1972,6 +2047,14 @@ CopyOneRowTo(CopyState cstate, Oid tupleOid, Datum *values, bool *nulls) CopySendInt32(cstate, tupleOid); } } + else if (cstate->raw) + { + if (cstate->row_processed) + ereport(ERROR, + (errcode(ERRCODE_TOO_MANY_ROWS), + errmsg("only single row result is allowed in RAW mode"))); + cstate->row_processed = true; + } else { /* Text format has no per-tuple header, but send OID if wanted */ @@ -1991,7 +2074,7 @@ CopyOneRowTo(CopyState cstate, Oid tupleOid, Datum *values, bool *nulls) Datum value = values[attnum - 1]; bool isnull = nulls[attnum - 1]; - if (!cstate->binary) + if (!cstate->binary && !cstate->raw) { if (need_delim) CopySendChar(cstate, cstate->delim[0]); @@ -2000,14 +2083,32 @@ CopyOneRowTo(CopyState cstate, Oid tupleOid, Datum *values, bool *nulls) if (isnull) { - if (!cstate->binary) + if (cstate->raw) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("cannot to copy NULL value in RAW mode."))); + else if (!cstate->binary) CopySendString(cstate, cstate->null_print_client); else CopySendInt32(cstate, -1); } else { - if (!cstate->binary) + if (cstate->binary || cstate->raw) + { + bytea *outputbytes; + + outputbytes = SendFunctionCall(&out_functions[attnum - 1], + value); + + /* send the size only in binary mode */ + if (cstate->binary) + CopySendInt32(cstate, VARSIZE(outputbytes) - VARHDRSZ); + + CopySendData(cstate, VARDATA(outputbytes), + VARSIZE(outputbytes) - VARHDRSZ); + } + else { string = OutputFunctionCall(&out_functions[attnum - 1], value); @@ -2018,16 +2119,6 @@ CopyOneRowTo(CopyState cstate, Oid tupleOid, Datum *values, bool *nulls) else CopyAttributeOutText(cstate, string); } - else - { - bytea *outputbytes; - - outputbytes = SendFunctionCall(&out_functions[attnum - 1], - value); - CopySendInt32(cstate, VARSIZE(outputbytes) - VARHDRSZ); - CopySendData(cstate, VARDATA(outputbytes), - VARSIZE(outputbytes) - VARHDRSZ); - } } } @@ -2657,7 +2748,7 @@ BeginCopyFrom(Relation rel, continue; /* Fetch the input function and typioparam info */ - if (cstate->binary) + if (cstate->binary || cstate->raw) getTypeBinaryInputInfo(attr[attnum - 1]->atttypid, &in_func_oid, &typioparams[attnum - 1]); else @@ -2752,7 +2843,7 @@ BeginCopyFrom(Relation rel, } } - if (!cstate->binary) + if (!cstate->binary || cstate->raw) { /* must rely on user to tell us... */ cstate->file_has_oids = cstate->oids; @@ -2804,7 +2895,7 @@ BeginCopyFrom(Relation rel, } /* create workspace for CopyReadAttributes results */ - if (!cstate->binary) + if (!cstate->binary && !cstate->raw) { AttrNumber attr_count = list_length(cstate->attnumlist); int nfields = cstate->file_has_oids ? (attr_count + 1) : attr_count; @@ -2909,8 +3000,120 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext, MemSet(values, 0, num_phys_attrs * sizeof(Datum)); MemSet(nulls, true, num_phys_attrs * sizeof(bool)); - if (!cstate->binary) + if (cstate->binary) + { + int16 fld_count; + ListCell *cur; + + cstate->cur_lineno++; + + if (!CopyGetInt16(cstate, &fld_count)) + { + /* EOF detected (end of file, or protocol-level EOF) */ + return false; + } + + if (fld_count == -1) + { + /* + * Received EOF marker. In a V3-protocol copy, wait for the + * protocol-level EOF, and complain if it doesn't come + * immediately. This ensures that we correctly handle CopyFail, + * if client chooses to send that now. + * + * Note that we MUST NOT try to read more data in an old-protocol + * copy, since there is no protocol-level EOF marker then. We + * could go either way for copy from file, but choose to throw + * error if there's data after the EOF marker, for consistency + * with the new-protocol case. + */ + char dummy; + + if (cstate->copy_dest != COPY_OLD_FE && + CopyGetData(cstate, &dummy, 1, 1) > 0) + ereport(ERROR, + (errcode(ERRCODE_BAD_COPY_FILE_FORMAT), + errmsg("received copy data after EOF marker"))); + return false; + } + + if (fld_count != attr_count) + ereport(ERROR, + (errcode(ERRCODE_BAD_COPY_FILE_FORMAT), + errmsg("row field count is %d, expected %d", + (int) fld_count, attr_count))); + + if (file_has_oids) + { + Oid loaded_oid; + + cstate->cur_attname = "oid"; + loaded_oid = + DatumGetObjectId(CopyReadBinaryAttribute(cstate, + 0, + &cstate->oid_in_function, + cstate->oid_typioparam, + -1, + &isnull)); + if (isnull || loaded_oid == InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_BAD_COPY_FILE_FORMAT), + errmsg("invalid OID in COPY data"))); + cstate->cur_attname = NULL; + if (cstate->oids && tupleOid != NULL) + *tupleOid = loaded_oid; + } + + i = 0; + foreach(cur, cstate->attnumlist) + { + int attnum = lfirst_int(cur); + int m = attnum - 1; + + cstate->cur_attname = NameStr(attr[m]->attname); + i++; + values[m] = CopyReadBinaryAttribute(cstate, + i, + &in_functions[m], + typioparams[m], + attr[m]->atttypmod, + &nulls[m]); + cstate->cur_attname = NULL; + } + } + else if (cstate->raw) { + if (cstate->row_processed) + return false; + + CopyLoadAllRawBuf(cstate); + cstate->cur_attname = NameStr(attr[0]->attname); + + if (cstate->attribute_buf.data != NULL) + pfree(cstate->attribute_buf.data); + + cstate->attribute_buf.data = cstate->raw_buf; + cstate->attribute_buf.len = cstate->raw_buf_len; + cstate->attribute_buf.cursor = 0; + + cstate->raw_buf = NULL; + + /* Call the column type's binary input converter */ + values[0] = ReceiveFunctionCall(&in_functions[0], &cstate->attribute_buf, + typioparams[0], attr[0]->atttypmod); + nulls[0] = false; + + /* Trouble if it didn't eat the whole buffer */ + if (cstate->attribute_buf.cursor != cstate->attribute_buf.len) + ereport(ERROR, + (errcode(ERRCODE_INVALID_BINARY_REPRESENTATION), + errmsg("incorrect binary data format"))); + + cstate->row_processed = true; + } + else + { + /* text */ char **field_strings; ListCell *cur; int fldct; @@ -3015,88 +3218,6 @@ NextCopyFrom(CopyState cstate, ExprContext *econtext, Assert(fieldno == nfields); } - else - { - /* binary */ - int16 fld_count; - ListCell *cur; - - cstate->cur_lineno++; - - if (!CopyGetInt16(cstate, &fld_count)) - { - /* EOF detected (end of file, or protocol-level EOF) */ - return false; - } - - if (fld_count == -1) - { - /* - * Received EOF marker. In a V3-protocol copy, wait for the - * protocol-level EOF, and complain if it doesn't come - * immediately. This ensures that we correctly handle CopyFail, - * if client chooses to send that now. - * - * Note that we MUST NOT try to read more data in an old-protocol - * copy, since there is no protocol-level EOF marker then. We - * could go either way for copy from file, but choose to throw - * error if there's data after the EOF marker, for consistency - * with the new-protocol case. - */ - char dummy; - - if (cstate->copy_dest != COPY_OLD_FE && - CopyGetData(cstate, &dummy, 1, 1) > 0) - ereport(ERROR, - (errcode(ERRCODE_BAD_COPY_FILE_FORMAT), - errmsg("received copy data after EOF marker"))); - return false; - } - - if (fld_count != attr_count) - ereport(ERROR, - (errcode(ERRCODE_BAD_COPY_FILE_FORMAT), - errmsg("row field count is %d, expected %d", - (int) fld_count, attr_count))); - - if (file_has_oids) - { - Oid loaded_oid; - - cstate->cur_attname = "oid"; - loaded_oid = - DatumGetObjectId(CopyReadBinaryAttribute(cstate, - 0, - &cstate->oid_in_function, - cstate->oid_typioparam, - -1, - &isnull)); - if (isnull || loaded_oid == InvalidOid) - ereport(ERROR, - (errcode(ERRCODE_BAD_COPY_FILE_FORMAT), - errmsg("invalid OID in COPY data"))); - cstate->cur_attname = NULL; - if (cstate->oids && tupleOid != NULL) - *tupleOid = loaded_oid; - } - - i = 0; - foreach(cur, cstate->attnumlist) - { - int attnum = lfirst_int(cur); - int m = attnum - 1; - - cstate->cur_attname = NameStr(attr[m]->attname); - i++; - values[m] = CopyReadBinaryAttribute(cstate, - i, - &in_functions[m], - typioparams[m], - attr[m]->atttypmod, - &nulls[m]); - cstate->cur_attname = NULL; - } - } /* * Now compute and insert any defaults available for the columns not diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out index 5e31737..30e77ca 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -469,3 +469,16 @@ DROP FUNCTION truncate_in_subxact(); DROP TABLE x, y; DROP FUNCTION fn_x_before(); DROP FUNCTION fn_x_after(); +CREATE TABLE x(a bytea); +INSERT INTO x VALUES('\x41484f4a0a'); +INSERT INTO x VALUES('\x41484f4a0a'); +-- should to fail +COPY (SELECT a,a FROM x LIMIT 1) TO STDOUT (FORMAT raw); +ERROR: only single column result is allowed in RAW mode +COPY (SELECT a FROM x) TO STDOUT (FORMAT raw); +AHOJ +ERROR: only single row result is allowed in RAW mode +-- should be ok +COPY (SELECT a FROM x LIMIT 1) TO STDOUT (FORMAT raw); +AHOJ +DROP TABLE x; diff --git a/src/test/regress/input/copy.source b/src/test/regress/input/copy.source index cb13606..d8970b1 100644 --- a/src/test/regress/input/copy.source +++ b/src/test/regress/input/copy.source @@ -133,3 +133,36 @@ this is just a line full of junk that would error out if parsed \. copy copytest3 to stdout csv header; + +-- copy raw +CREATE TABLE x(a bytea); +INSERT INTO x VALUES('\x41484f4a0a'); +SELECT length(a) FROM x; + +INSERT INTO x VALUES('\x41484f4a0a'); + +-- should to fail +COPY (SELECT a,a FROM x LIMIT 1) TO '@abs_builddir@/results/raw.data' (FORMAT raw); +COPY (SELECT a FROM x) TO '@abs_builddir@/results/raw.data' (FORMAT raw); + +-- should be ok +COPY (SELECT a FROM x LIMIT 1) TO '@abs_builddir@/results/raw.data' (FORMAT raw); +TRUNCATE x; +COPY x FROM '@abs_builddir@/results/raw.data' (FORMAT raw); +SELECT length(a) FROM x; +COPY x TO stdout (FORMAT raw); + +TRUNCATE x; + +\COPY x FROM '@abs_builddir@/results/raw.data' (FORMAT raw) +SELECT length(a) FROM x; +COPY x TO stdout (FORMAT raw); + +\COPY x TO '@abs_builddir@/results/raw2.data' (FORMAT raw) +TRUNCATE x; + +\COPY x FROM '@abs_builddir@/results/raw2.data' (FORMAT raw) +SELECT length(a) FROM x; +COPY x TO stdout (FORMAT raw); + +DROP TABLE x; diff --git a/src/test/regress/output/copy.source b/src/test/regress/output/copy.source index b7e372d..878797a 100644 --- a/src/test/regress/output/copy.source +++ b/src/test/regress/output/copy.source @@ -95,3 +95,52 @@ copy copytest3 to stdout csv header; c1,"col with , comma","col with "" quote" 1,a,1 2,b,2 +-- copy raw +CREATE TABLE x(a bytea); +INSERT INTO x VALUES('\x41484f4a0a'); +SELECT length(a) FROM x; + length +-------- + 5 +(1 row) + +INSERT INTO x VALUES('\x41484f4a0a'); +-- should to fail +COPY (SELECT a,a FROM x LIMIT 1) TO '@abs_builddir@/results/raw.data' (FORMAT raw); +ERROR: only single column result is allowed in RAW mode +COPY (SELECT a FROM x) TO '@abs_builddir@/results/raw.data' (FORMAT raw); +ERROR: only single row result is allowed in RAW mode +-- should be ok +COPY (SELECT a FROM x LIMIT 1) TO '@abs_builddir@/results/raw.data' (FORMAT raw); +TRUNCATE x; +COPY x FROM '@abs_builddir@/results/raw.data' (FORMAT raw); +SELECT length(a) FROM x; + length +-------- + 5 +(1 row) + +COPY x TO stdout (FORMAT raw); +AHOJ +TRUNCATE x; +\COPY x FROM '@abs_builddir@/results/raw.data' (FORMAT raw) +SELECT length(a) FROM x; + length +-------- + 5 +(1 row) + +COPY x TO stdout (FORMAT raw); +AHOJ +\COPY x TO '@abs_builddir@/results/raw2.data' (FORMAT raw) +TRUNCATE x; +\COPY x FROM '@abs_builddir@/results/raw2.data' (FORMAT raw) +SELECT length(a) FROM x; + length +-------- + 5 +(1 row) + +COPY x TO stdout (FORMAT raw); +AHOJ +DROP TABLE x; diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql index 39a9deb..e5703a5 100644 --- a/src/test/regress/sql/copy2.sql +++ b/src/test/regress/sql/copy2.sql @@ -333,3 +333,16 @@ DROP FUNCTION truncate_in_subxact(); DROP TABLE x, y; DROP FUNCTION fn_x_before(); DROP FUNCTION fn_x_after(); + +CREATE TABLE x(a bytea); +INSERT INTO x VALUES('\x41484f4a0a'); +INSERT INTO x VALUES('\x41484f4a0a'); + +-- should to fail +COPY (SELECT a,a FROM x LIMIT 1) TO STDOUT (FORMAT raw); +COPY (SELECT a FROM x) TO STDOUT (FORMAT raw); + +-- should be ok +COPY (SELECT a FROM x LIMIT 1) TO STDOUT (FORMAT raw); + +DROP TABLE x;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers