Thread: putting binary data in a char field?

I'm attempting to use spamassassin 3.0 (beta) with an SQL backend, and have
identified one performance gain so far that makes PostgreSQL a good (IMO)
candidate for the backend.  I need some advice though on another aspect--
instead of storing each token as text in the database, the token is being
sha1()'d and then possibly pack()'d.

That's all just background for my real question-- is there anything in the
standards (or elsewhere) that says you can't put binary(**) data into a char
field?  When I changed the field to a bytea, processing time was
significantly reduced.

(**) Based on what very little I know of sha1 and of the tokens that have
been created by SA, it certainly looks like it's binary data that is being
inserted into the char field.

Thanks,
-ron

Ron Snyder <snyder@roguewave.com> writes:
> That's all just background for my real question-- is there anything in the
> standards (or elsewhere) that says you can't put binary(**) data into a char
> field?  When I changed the field to a bytea, processing time was
> significantly reduced.

bytea is the right thing.  char will not support null (zero) bytes in
the data, and you may have character-set-encoding issues as well.

            regards, tom lane

Ron Snyder <snyder@roguewave.com> wrote:
> When I changed the field to a bytea, processing time was
> significantly reduced

You should also experiment with using a TEXT field and base64 encoding to insert data and base64
decoding to select data.  In my experience, this is faster than using a bytea field.  Certain
characters in binary data have to be escaped properly before you can insert data.  The size and
time this takes can vary.  Base64 encoding is fast and will increase the binary string by 1/3.

George Essig

> You should also experiment with using a TEXT field and base64
> encoding to insert data and base64 decoding to select data.
> In my experience, this is faster than using a bytea field.
> Certain characters in binary data have to be escaped properly
> before you can insert data.  The size and time this takes can
> vary.  Base64 encoding is fast and will increase the binary
> string by 1/3.

Hmm, I may give that a try.  Thanks,

-ron