Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 7DE9D133798E
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Wed, 15 Sep 2010 22:26:49 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 79500-01-3
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 16 Sep 2010 01:26:42 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.187])
	by mail.postgresql.org (Postfix) with ESMTP id E7DC413380FD
	for <pgsql-admin@postgresql.org>; Wed, 15 Sep 2010 22:26:41 -0300 (ADT)
Received: from [192.168.1.6] (static-71-179-240-8.bltmmd.fios.verizon.net
	[71.179.240.8])
	by mrelayeu.kundenserver.de (node=mrbap2) with ESMTP (Nemesis)
	id 0M6xID-1Oaw1o2y85-00x8Iq; Thu, 16 Sep 2010 03:26:38 +0200
Message-ID: <4C91724C.4040802@2ndquadrant.com>
Date: Wed, 15 Sep 2010 21:26:36 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Lou Picciano <loupicciano@comcast.net>
CC: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: How To: LARGE html text or csv file COPY FROM?
References: 
 <594168409.400001.1284555796001.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net>
In-Reply-To: 
 <594168409.400001.1284555796001.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net>
Content-Type: multipart/alternative;
	boundary="------------080000090109010001080706"
X-Provags-ID: V02:K0:E8fssr8Y7wwKe0A8P3/1BQZY1YtMcPKH44crpeyrS7o
	wz64KI3z0HSm84+PtorqWazZhYINgayn2a2JeDKtN44wFwlheB
	s6xI9NBA8jzYRBtunb2NjydFoMgMiRE0/yo6g/QAXoO5drWoEQ
	f1n9KTaKNdBWQWOuQZd1EodJRnNGuwCr9g0HtKr1BcZKvu2ozw
	KCCyqHwaKtXO0lXip06xVwufY0QnbL7OaJO082/ZbE=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201009/83
X-Sequence-Number: 35627

This is a multi-part message in MIME format.
--------------080000090109010001080706
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Lou Picciano wrote:p { margin: 0; }
> Both errors above make sense to me; COPY is running into either a cr 
> or a tab character in each case.

Even though they might seem very similar at first, text mode and CSV 
mode work very differently here.  To quote TFM at 
http://www.postgresql.org/docs/current/static/sql-copy.html , which 
covers all this if you dig into it enough:

"CSV mode will both recognize and produce CSV files with quoted values 
containing embedded carriage returns and line feeds. Thus the files are 
not strictly one line per table row like text-mode files."

So as long as the field is quoted, you can have a CR or tab in the middle.

In text mode:  "the following characters /must/ be preceded by a 
backslash if they appear as part of a column value: backslash itself, 
newline, carriage return, and the current delimiter character."  Text 
mode will therefore require escaping everything on that list.

So if this is close to being importable in CSV mode, you might just need 
to wrap the HTML field with a pair of quotes, and escape any quote 
characters that are inside of it.  Text mode is going to require a whole 
escaping run of all the reserved characters that are input.

-- 
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"    Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book


--------------080000090109010001080706
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Lou Picciano wrote:p { margin: 0; }<br>
<blockquote
 cite="mid:594168409.400001.1284555796001.JavaMail.root@sz0093a.westchester.pa.mail.comcast.net"
 type="cite">
  <div
 style="font-family: Verdana; font-size: 12pt; color: rgb(0, 0, 0);">
  <div>Both errors above make sense to me; COPY is running into either
a cr or a tab character in each case.</div>
  </div>
</blockquote>
<br>
Even though they might seem very similar at first, text mode and CSV
mode work very differently here.  To quote TFM at
<a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/current/static/sql-copy.html">http://www.postgresql.org/docs/current/static/sql-copy.html</a> , which
covers all this if you dig into it enough:<br>
<br>
"CSV mode will both recognize and produce CSV files with quoted values
containing embedded carriage returns and line feeds. Thus the files are
not strictly one line per table row like text-mode files."<br>
<br>
So as long as the field is quoted, you can have a CR or tab in the
middle.<br>
<br>
In text mode:  "the following characters <span class="emphasis"><i
 class="EMPHASIS">must</i></span>
be preceded by a backslash if they appear as part of a column value:
backslash itself, newline, carriage return, and the current delimiter
character."  Text mode will therefore require escaping everything on
that list.<br>
<br>
So if this is close to being importable in CSV mode, you might just
need to wrap the HTML field with a pair of quotes, and escape any quote
characters that are inside of it.  Text mode is going to require a
whole escaping run of all the reserved characters that are input.<br>
<br>
<pre class="moz-signature" cols="72">-- 
Greg Smith, 2ndQuadrant US <a class="moz-txt-link-abbreviated" href="mailto:greg@2ndQuadrant.com">greg@2ndQuadrant.com</a> Baltimore, MD
PostgreSQL Training, Services and Support  <a class="moz-txt-link-abbreviated" href="http://www.2ndQuadrant.us">www.2ndQuadrant.us</a>
Author, "PostgreSQL 9.0 High Performance"    Pre-ordering at:
<a class="moz-txt-link-freetext" href="https://www.packtpub.com/postgresql-9-0-high-performance/book">https://www.packtpub.com/postgresql-9-0-high-performance/book</a>
</pre>
</body>
</html>

--------------080000090109010001080706--