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

Reply via email to