Fwd: Re: bytea and text
От | Syan Tan |
---|---|
Тема | Fwd: Re: bytea and text |
Дата | |
Msg-id | 57324.1259494191@people.net.au обсуждение исходный текст |
Список | 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@lightlink.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@sss.pgh > .pa.us 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: > >> http://www.postgresql.org/docs/8.4/static/lo-examplesect.html >> 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 > N2SPP > > > -- > Sent via pgsql-novice mailing list (p > gsql-novice@postgresql.org)To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > >
В списке pgsql-novice по дате отправления: