Skip site navigation (1) Skip section navigation (2)

Fwd: Re: bytea and text

From: Syan Tan <kittylitter(at)people(dot)net(dot)au>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Fwd: Re: bytea and text
Date: 2009-11-29 11:29:51
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice

sorry, just have faulty memory from doing it months ago with both derbydb and
postgresql as
alternative drivers. Derbydb was the one that preferred intermediate files for
bulk loading of
binary stuff as hex  I think but there was no delimiters apart from comma for
that , whereas 
postgresql allows you to escape every character as a slash followed by 1-3 digit
octal or 1-2 digit hex preceded
by a slash and a x , so that was the point of the question, whether is it better
to be paranoid that a 
bulk load is going to fail and escape every character, or can you be optimistic
an just escape
newline carriage return , backslash itself and the current delimiter character ,
but is there an end of file
sequence ? and doing you have to escape the null sequence ? \N if it happens to
occur in your raw binary data.
If a 3 gig bulk load fails because of one character, it's annoying.
   I remember doing the same thing as individual pgdb api calls a few years ago, 
and having no such problem in python with binary data , or at least you could
fail one image and not have it affect the rest of the rows by having autocommit on.

On Sat 28/11/09 13:19 , Bob McConnell rmcconne(at)lightlink(dot)com sent:
> Syan Tan wrote:
> > here's a quote from the postgresql
> documentation> 
> > 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.> 
> > So if I scan a field value ( say a big buffer to
> hold an entire> image), and have a second buffer to copy an
> escaped image string to ,> and I read one of the 4 characters mentioned
> above, and insert a> backslash into the destination buffer, and then
> continue copying into> the second buffer, I should end up with a string
> that I can put> inside a comma-separated text file ( so comma =
> the current delimiter> character ), and then I can bulk load a huge
> text file containing> thousands of images without problems using
> COPY..FROM , is that > correct ?
> > 
> > 
> > On Sat 28/11/09 01:29 , "Tom Lane" tgl(at)sss(dot)pgh
> sent:>> richard terry 
> writes:> tom, I wonder if>> you could give us a sample of using client
> side lo_creat , > insert>> functions to insert a blob into postgres -
> in an sql statement. >> There's an example program in the docs:
> >> >> regards,
tom lane
> It is my understanding that the current SQL standard defines the single
> quote as the official escape character. While the DB engine accepts 
> others, wouldn't it be best to use the official character?
> Bob McConnell
> -- 
> Sent via pgsql-novice mailing list (p
> gsql-novice(at)postgresql(dot)org)To make changes to your subscription:

pgsql-novice by date

Next:From: Kris KewleyDate: 2009-11-29 13:41:58
Subject: Re: bytea and text
Previous:From: Jasen BettsDate: 2009-11-29 08:34:28
Subject: Re: bytea and text

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group