Re: COPY complaining about unquoted carriage return found in data... in a quoted field

On Tue, 10 May 2011 15:59:07 +0200
Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> wrote:

Sorry for the noise. The csv was automatically generated. The code
was right but during generation there was some problem with the box
generating it (php segfaulting) and there were some unclosed quotes
in a much earlier line.

Postgresql actually behaves as documented and expected... and the
documentation is clear.

> On Tue, 10 May 2011 14:38:23 +0200
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
> > Hello
> >
> > COPY doesn't like '\n' too.
> >
> > Replace '\n' by '\\n'
>
> mmm maybe you were mislead by the "semi-transliterated" hexdump.
>
> There is no "slash" in the record, the actual input was the one
> reported in hex. The following line was just to help reading the
> hexdump.
>
> 09 22 56 | 69 61 20 46 | 61 65 6E 74 | 69 6E 61 2C
> 20 35 33 0D | 0A 22 09 22 | 35 30 30 31 | 34 22 09
>
> as a quick reference:
> 0x09 = \t
> 0x0a = \n
> 0x0d = \r
> 0x20 = space
> 0x22 = "
>
> I thought that
>
> csv
> quote as '"'
>
> where there exactly to avoid escaping of delimiters (other than
> the quoting character itself) inside fields.
>
> From the docs:
> http://www.postgresql.org/docs/8.3/static/sql-copy.html
> «If the value contains the delimiter character, the QUOTE
> character, the NULL string, a carriage return, or line feed
> character, then the whole value is prefixed and suffixed by the
> QUOTE character, and any occurrence within the value of a QUOTE
> character or the ESCAPE character is preceded by the escape
> character.»
>
> So up to my understanding once I've "quoted" a field I've to take
> care of escaping just the quote and the escape characters and the
> field may contain delimiter characters (field separator and record
> separator) without escaping.
>
> I was wondering if a) the documentation is wrong b) I didn't write
> a correct \COPY command string c) there is a bug d) I can't
> correctly interpret the documentation e) I'm more stupid then usual
>
> thanks
>

--
Ivan Sergio Borgonovo
http://www.webthatworks.it