I wrote:
> It seems the psql \copy command doesn't work properly when dealing
> with certain non-ascii characters (which ones I don't know). At any rate
> I took some binary data and escaped it like so with perl.
I was wrong. I wasn't escaping things properly; in particular,
ascii 0 needed to be escaped to \\000 . Also I was using 'text' type
instead of 'bytea' type. I thought 'copy' was working but it was
actually cutting off my input at the first ascii zero, whereas \copy
gave me the error message. Sorry to waste people's time.
In the event anyone else is out there who wants to use binary data
with 'copy from' and is using perl to do the escaping this seems to
work.
# double up the escape character twice! Once for the 'copy from' command and
# once for postgres bytea type escaping.
$text=~s/\\/\\\\\\\\/g;
$text=~s/\n/\\\n/g; # escape \n
$text=~s/\t/\\\t/g; # escape \t
$text=~s/'/\\'/g; # escape '
$text=~s/\x00/\\\\000/g; # escape ascii 0
