Ok, so, stupid question: how does all this interact with COPY escaping
rules[1]?:
Backslash characters (\) can be used in the COPY data to quote
data characters that might otherwise be taken as row or column
delimiters. In particular, 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.
I imagined that this would be just another layer, but in my initial
tests, I was not doing this, and things worked. When I add it in, I
get too many levels of escaping. I can see this with even something
fairly simple:
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.4 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
(1 row)
postgres=# create type foo as (a text);
CREATE TYPE
postgres=# create table bar(a foo[]);
CREATE TABLE
postgres=# copy bar from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"(\\\\"baz\\\\")}
>> \.
ERROR: malformed array literal: "{"(\\"baz\\")}"
CONTEXT: COPY bar, line 1, column a: "{"(\\"baz\\")}"
postgres=# show standard_conforming_strings;
standard_conforming_strings
-----------------------------
off
(1 row)
My escaping logic for the above COPY input: "baz" is double-quoted as
a value in a UDT. When I put that UDT in an array literal, I enclose
it in double quotes and I precede all double quote characters with a
backslash. Because of standard_conforming_strings being off, I escape
all backslashes with backslashes (so I now have two backslashes and a
quote). Because of text-mode COPY, I escape each of those again (so I
now have four backslashes and a quote).
I seem to be missing something. Does standard_conforming_strings not
apply during COPY? Or is there something else I'm missing?
Thanks,
[1]: http://www.postgresql.org/docs/8.3/static/sql-copy.html
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
www.truviso.com
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
