Examples of using bytea type

rfchurch@hotmail.com wrote:

The current PostgreSQL documentation is does not discuss the bytea type at
all.

Does anyone have indepth examples of using this type in SQL?

I know expressing BLOb data in SQL is awkward and would generate huge text
files, but is there a standard method of using escape sequences to express
blobs or something similar to MIME?

There surely must be something like that.

The bytea type is undocumented, so I suppose you should use it at your
own risk, since it may vanish (like lztext did at 7.1).

Keep in mind that unless you use binary cursors, what you get back from a
query on a bytea type is a string with "\nnn" sequences mixed with text in
it, which your application would then need to decode to get the actual
data. For inserting, you must convert your data to \nnn sequences (\\000
for 0), all in one string, to use in an insert on a bytea (there are no
binary cursors for insert). So it's a lot of trouble, and might not be
worth the effort. If you have to encode/decode in your application anyway,
I wonder if it wouldn't be be easier to do Base64 or Ascii85 coding and use
the "text" type in PostgreSQL instead of bytea?

On Fri, 20 Jul 2001, ljb wrote:

rfchurch@hotmail.com wrote:

The current PostgreSQL documentation is does not discuss the bytea type at
all.

Does anyone have indepth examples of using this type in SQL?

I know expressing BLOb data in SQL is awkward and would generate huge text
files, but is there a standard method of using escape sequences to express
blobs or something similar to MIME?

There surely must be something like that.

The bytea type is undocumented, so I suppose you should use it at your
own risk, since it may vanish (like lztext did at 7.1).

Keep in mind that unless you use binary cursors, what you get back from a
query on a bytea type is a string with "\nnn" sequences mixed with text in
it, which your application would then need to decode to get the actual
data. For inserting, you must convert your data to \nnn sequences (\\000
for 0), all in one string, to use in an insert on a bytea (there are no
binary cursors for insert). So it's a lot of trouble, and might not be
worth the effort. If you have to encode/decode in your application anyway,
I wonder if it wouldn't be be easier to do Base64 or Ascii85 coding and use
the "text" type in PostgreSQL instead of bytea?

Bytea IS documented in 7.2-devel and is definitely not going away. Major
benefit of bytea over base64/whatever encoding is storage space. Need to
escape/unescape input data depends on your frontend client. Perl DBD::Pg
will do it transparently without needing to do \\ escapes.

In 7.2, there will also be functions base64_encode/base64_decode for bytea
types. In 7.1, they exist under contrib/pg_crypto

-alex

Bytea IS documented in 7.2-devel and is definitely not going away. Major
benefit of bytea over base64/whatever encoding is storage space. Need to
escape/unescape input data depends on your frontend client. Perl DBD::Pg
will do it transparently without needing to do \\ escapes.

In 7.2, there will also be functions base64_encode/base64_decode for bytea
types. In 7.1, they exist under contrib/pg_crypto

Do they need to be removed from 7.2 /contrib? Please tell me how.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Fri, 20 Jul 2001, Bruce Momjian wrote:

Bytea IS documented in 7.2-devel and is definitely not going away. Major
benefit of bytea over base64/whatever encoding is storage space. Need to
escape/unescape input data depends on your frontend client. Perl DBD::Pg
will do it transparently without needing to do \\ escapes.

In 7.2, there will also be functions base64_encode/base64_decode for bytea
types. In 7.1, they exist under contrib/pg_crypto

Do they need to be removed from 7.2 /contrib? Please tell me how.

Nono. Ones in /contrib are much more than encoding, its whole crypto
setup. Ones in core are slightly modified versions of them which only take
care of encode/decode.

-alex