Обсуждение: How to store Byte strings into a table.
Hi,<br /><br />How can I store Byte strings into a postgresql database. <br /><br />Is there any special command to storeit. How will be the sql query.<br /><br />I have gone through this <a href="http://www.postgresql.org/docs/8.4/static/datatype-binary.html">http://www.postgresql.org/docs/8.4/static/datatype-binary.html</a> buthave not seen any example for how to insert data into such a field.<br /><br />Please help me in this regard.<br /><brclear="all" /><br />-- <br />Regards,<br />----------------------------------------------<br />Trinath Somanchi,<br/>
On 07/05/10 09:57, wrote:
> Hi,
>
> How can I store Byte strings into a postgresql database.
> Is there any special command to store it. How will be the sql query.
there is only '\0' byte incapable to input-output.
so u have to have it escaped at all costs _AND NOTHING MORE_.
"escaped" doesn't mean "prefixed with backslash"
("backslash method" cause a zero-byte to pass SQL parser an to be
actually stored, BUT
the output will be corrupted, because of this zero-byte will be actually
output)
You may use the BYTEA type
(similar to the TEXT but with different input-output) which effectively
escapes zero-byte and a lot of other completely harmless bytes as well
(probably to reach a better overhead)
Or you may introduce a pair of your own escape rules.
Unfortunately there are no way to influence The Pg Developers to get rid
of the nasty god damned CSTRING off the input/output operations.
Hi,<br /><br />I'm new in using BLOB. How will the insert for storing very large byte strings into a column of data
typeBlob.<br /><br /><div class="gmail_quote">On Mon, Jul 5, 2010 at 11:42 AM, silly sad <span dir="ltr"><<a
href="mailto:sad@bankir.ru">sad@bankir.ru</a>></span>wrote:<br /><blockquote class="gmail_quote" style="border-left:
1pxsolid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">On 07/05/10 09:57, wrote:<br /><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi,<br/><br /> How can I store Byte strings into a postgresql database.<br /> Is there any special command to store it.
Howwill be the sql query.<br /></blockquote><br /> there is only '\0' byte incapable to input-output.<br /> so u have
tohave it escaped at all costs _AND NOTHING MORE_.<br /><br /> "escaped" doesn't mean "prefixed with backslash"<br />
("backslashmethod" cause a zero-byte to pass SQL parser an to be actually stored, BUT<br /> the output will be
corrupted,because of this zero-byte will be actually output)<br /><br /> You may use the BYTEA type<br /> (similar to
theTEXT but with different input-output) which effectively escapes zero-byte and a lot of other completely harmless
bytesas well (probably to reach a better overhead)<br /><br /> Or you may introduce a pair of your own escape rules.<br
/><br/> Unfortunately there are no way to influence The Pg Developers to get rid of the nasty god damned CSTRING off
theinput/output operations.<br /><font color="#888888"><br /><br /> -- <br /> Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@postgresql.org"target="_blank">pgsql-sql@postgresql.org</a>)<br /> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br /><br clear="all"
/><br/>-- <br />Regards,<br />----------------------------------------------<br />Trinath Somanchi,<br />
Hello use a LO interface http://www.postgresql.org/docs/8.4/static/lo-interfaces.html exact form depends on language that you are use. Regards Pavel Stehule 2010/7/5 Trinath Somanchi <trinath.somanchi@gmail.com>: > Hi, > > I'm new in using BLOB. How will the insert for storing very large byte > strings into a column of data type Blob. > > On Mon, Jul 5, 2010 at 11:42 AM, silly sad <sad@bankir.ru> wrote: >> >> On 07/05/10 09:57, wrote: >>> >>> Hi, >>> >>> How can I store Byte strings into a postgresql database. >>> Is there any special command to store it. How will be the sql query. >> >> there is only '\0' byte incapable to input-output. >> so u have to have it escaped at all costs _AND NOTHING MORE_. >> >> "escaped" doesn't mean "prefixed with backslash" >> ("backslash method" cause a zero-byte to pass SQL parser an to be actually >> stored, BUT >> the output will be corrupted, because of this zero-byte will be actually >> output) >> >> You may use the BYTEA type >> (similar to the TEXT but with different input-output) which effectively >> escapes zero-byte and a lot of other completely harmless bytes as well >> (probably to reach a better overhead) >> >> Or you may introduce a pair of your own escape rules. >> >> Unfortunately there are no way to influence The Pg Developers to get rid >> of the nasty god damned CSTRING off the input/output operations. >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > > > -- > Regards, > ---------------------------------------------- > Trinath Somanchi, >
On 07/05/10 10:30, Trinath Somanchi wrote: > Hi, > > I'm new in using BLOB. How will the insert for storing very large byte > strings into a column of data type Blob. i didn't advice you to use BLOB. you may store a string as long as 2GB at any TEXT or BYTEA field.
2010/7/5 silly sad <sad@bankir.ru>: > On 07/05/10 10:30, Trinath Somanchi wrote: >> >> Hi, >> >> I'm new in using BLOB. How will the insert for storing very large byte >> strings into a column of data type Blob. > > i didn't advice you to use BLOB. > > you may store a string as long as 2GB at any TEXT or BYTEA field. you can do it, but don't do it! Escaping of large strings are not cheap, processing extra long SQL statements are extreme expensive on memory - so don't do it - or test it before and check memory and processor usage - and check it in testing environment with more than one user. The good size for text or bytea is less than 100M and real max isn't 2G but it is 1G. LO isn't these limits because it isn't accessable on SQL level. Regards Pavel Stehule > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
On 5 July 2010 09:57, Trinath Somanchi <trinath.somanchi@gmail.com> wrote: > Hi, > > How can I store Byte strings into a postgresql database. > > Is there any special command to store it. How will be the sql query. > > I have gone through this > http://www.postgresql.org/docs/8.4/static/datatype-binary.html but have not > seen any example for how to insert data into such a field. Use octet-escaping to form the string to put in your SQL like it described it the article you referenced. > > Please help me in this regard. > > > -- > Regards, > ---------------------------------------------- > Trinath Somanchi, > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
On 07/05/10 10:43, Pavel Stehule wrote: > The good size for text or bytea is less than 100M and real max isn't > 2G but it is 1G. LO isn't these limits because it isn't accessable on > SQL level. any regular file on my filesystem isn't accessible on SQL level. i am happy with them and never tried to store at a database.
2010/7/5 silly sad <sad@bankir.ru>: > On 07/05/10 10:43, Pavel Stehule wrote: > >> The good size for text or bytea is less than 100M and real max isn't >> 2G but it is 1G. LO isn't these limits because it isn't accessable on >> SQL level. > > any regular file on my filesystem isn't accessible on SQL level. > i am happy with them and never tried to store at a database. this is second extreme - you can use everything if you know what you do - and mainly it depends on applications and requests that you have to solve. Pavel > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
On 07/05/10 11:03, Pavel Stehule wrote: > 2010/7/5 silly sad<sad@bankir.ru>: >> On 07/05/10 10:43, Pavel Stehule wrote: >> >>> The good size for text or bytea is less than 100M and real max isn't >>> 2G but it is 1G. LO isn't these limits because it isn't accessable on >>> SQL level. >> >> any regular file on my filesystem isn't accessible on SQL level. >> i am happy with them and never tried to store at a database. > > this is second extreme - you can use everything if you know what you > do - and mainly it depends on applications and requests that you have > to solve. the trouble is the initiator of the thread didn't determine what is his problem either storing of a zero-byte containing string or storing of huge strings. I answered him about BYTEA and he replied about BLOB. I only tried to say i didn't say a word about BLOB. P.S. Practically for storing pictures i prefer regular files.
2010/7/5 silly sad <sad@bankir.ru>: > On 07/05/10 11:03, Pavel Stehule wrote: >> >> 2010/7/5 silly sad<sad@bankir.ru>: >>> >>> On 07/05/10 10:43, Pavel Stehule wrote: >>> >>>> The good size for text or bytea is less than 100M and real max isn't >>>> 2G but it is 1G. LO isn't these limits because it isn't accessable on >>>> SQL level. >>> >>> any regular file on my filesystem isn't accessible on SQL level. >>> i am happy with them and never tried to store at a database. >> >> this is second extreme - you can use everything if you know what you >> do - and mainly it depends on applications and requests that you have >> to solve. > > the trouble is the initiator of the thread didn't determine what is his > problem either storing of a zero-byte containing string or storing of huge > strings. > I answered him about BYTEA and he replied about BLOB. > I only tried to say i didn't say a word about BLOB. yes > > P.S. > Practically for storing pictures i prefer regular files. > how I say - it depends on application - sometime can be useful have to access to all data only from db connect - for million small pictures the bytea can be best. Pavel
On 07/05/10 11:18, Pavel Stehule wrote: >> P.S. >> Practically for storing pictures i prefer regular files. >> > > how I say - it depends on application - sometime can be useful have to > access to all data only from db connect - for million small pictures > the bytea can be best. i really love postgres TEXT type, but i hate CSTRING input-output bottleneck.
2010/7/5 silly sad <sad@bankir.ru>: > On 07/05/10 11:18, Pavel Stehule wrote: > >>> P.S. >>> Practically for storing pictures i prefer regular files. >>> >> >> how I say - it depends on application - sometime can be useful have to >> access to all data only from db connect - for million small pictures >> the bytea can be best. > > i really love postgres TEXT type, but i hate CSTRING input-output why? and it isn't true - you can use a binary interface of PQexecParams - minimally for text and bytea it is very simple Pavel > bottleneck. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
I wrote an article covering this on the wiki http://wiki.postgresql.org/wiki/BinaryFilesInDB I need to update to for 9.0 as bytea now allows HEX format strings http://developer.postgresql.org/pgdocs/postgres/datatype-binary.html All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by ourproprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other informationproprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses.If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mailimmediately. Thank you.
Вложения
On 07/06/10 21:52, Justin Graf wrote: > I wrote an article covering this on the wiki > > http://wiki.postgresql.org/wiki/BinaryFilesInDB there are some "red flags" in communication (particularly reading papers) one of them is "binary data" which ITSELF IS NONSENCE.
On 7/7/2010 12:00 AM, silly sad wrote: > On 07/06/10 21:52, Justin Graf wrote: > >> I wrote an article covering this on the wiki >> >> http://wiki.postgresql.org/wiki/BinaryFilesInDB >> > there are some "red flags" in communication > (particularly reading papers) > one of them is "binary data" which ITSELF IS NONSENCE. > WHAT??????? You do understand that if you don't like it you can spend time fixing it. All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by ourproprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other informationproprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses.If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified thatany unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mailimmediately. Thank you.
Вложения
On 07/07/10 18:35, Justin Graf wrote: > On 7/7/2010 12:00 AM, silly sad wrote: >> On 07/06/10 21:52, Justin Graf wrote: >> >>> I wrote an article covering this on the wiki >>> >>> http://wiki.postgresql.org/wiki/BinaryFilesInDB >>> >> there are some "red flags" in communication >> (particularly reading papers) >> one of them is "binary data" which ITSELF IS NONSENCE. >> > > WHAT??????? > > You do understand that if you don't like it you can spend time fixing it. fix what? human minds of so-called programmers?
On Thu, Jul 8, 2010 at 8:31 AM, silly sad <sad@bankir.ru> wrote: > On 07/07/10 18:35, Justin Graf wrote: >> >> On 7/7/2010 12:00 AM, silly sad wrote: >>> >>> On 07/06/10 21:52, Justin Graf wrote: >>> >>>> I wrote an article covering this on the wiki >>>> >>>> http://wiki.postgresql.org/wiki/BinaryFilesInDB >>>> >>> there are some "red flags" in communication >>> (particularly reading papers) >>> one of them is "binary data" which ITSELF IS NONSENCE. >>> >> >> WHAT??????? >> >> You do understand that if you don't like it you can spend time fixing it. > > fix what? human minds of so-called programmers? That is not appropriate behaviour for the PostgreSQL mailing lists. Please treat others with respect, even if you disagree with them. -- Dave Page PostgreSQL Core Team
On 07/08/10 11:38, Dave Page wrote: > On Thu, Jul 8, 2010 at 8:31 AM, silly sad<sad@bankir.ru> wrote: >> On 07/07/10 18:35, Justin Graf wrote: >>> >>> On 7/7/2010 12:00 AM, silly sad wrote: >>>> >>>> On 07/06/10 21:52, Justin Graf wrote: >>>> >>>>> I wrote an article covering this on the wiki >>>>> >>>>> http://wiki.postgresql.org/wiki/BinaryFilesInDB >>>>> >>>> there are some "red flags" in communication >>>> (particularly reading papers) >>>> one of them is "binary data" which ITSELF IS NONSENCE. >>>> >>> >>> WHAT??????? >>> >>> You do understand that if you don't like it you can spend time fixing it. >> >> fix what? human minds of so-called programmers? > > That is not appropriate behaviour for the PostgreSQL mailing lists. > Please treat others with respect, even if you disagree with them. i do not disagree. i only meant a fixing of human communication problem is out of my hand.