Thread: RE: storing binary data - PGSQL book/documentation

RE: storing binary data - PGSQL book/documentation

From
Franck Martin
Date:
I'm not sure about this one, but could someone update the PG documentation
and book to describe bytea type.

Could someone explain also if there is an issue to store binary data in
bytea and retreive the data if there is a BIGENDINA/SMALLENDIAN setup.

Also can I create a user type which send back some binary data ?

Franck Martin
Database Development Officer
SOPAC South Pacific Applied Geoscience Commission
Fiji
E-mail: franck@sopac.org <mailto:franck@sopac.org>
Web site: http://www.sopac.org/ <http://www.sopac.org/>

This e-mail is intended for its recipients only. Do not forward this
e-mail without approval. The views expressed in this e-mail may not be
neccessarily the views of SOPAC.



-----Original Message-----
From: Alfred Perlstein [mailto:bright@wintelcom.net]
Sent: Tuesday, October 17, 2000 3:57 PM
To: PostgreSQL General
Subject: Re: [GENERAL] storing binary data


* Neil Conway <nconway@klamath.dyndns.org> [001016 20:41] wrote:
> On Mon, Oct 16, 2000 at 11:22:40PM -0400, Tom Lane wrote:
> > Neil Conway <nconway@klamath.dyndns.org> writes:
> > > I want to store some binary data in Postgres. The data is an
> > > MD5 checksum of the user's password, in binary. It will be
> > > exactly 16 bytes (since it is a one-way hash).
> >
> > > Can I store this safely in a CHAR column?
> >
> > No.  CHAR and friends assume there are no null (zero) bytes.
> > In MULTIBYTE setups there are probably additional constraints.
> >
> > You could use bytea, but I would recommend converting the checksum
> > to a hex digit string and then storing that in a char-type field.
> > Hex is the usual textual representation for MD5 values, no?
>
> It is, but (IMHO) it's a big waste of space. The actual MD5 digest is
> 128 bits. If stored in binary form, it's 16 bytes. If stored in hex
> form (as ASCII), it's 32 characters @ 1 byte per character = 32 bytes.
> In Unicode, that's 64 bytes (correct me if I'm wrong).
>
> It's not a huge deal, but it would be nice to store this efficiently.
> Is this possible?

Why not use base64?  It's pretty gross but might work for you.

-Alfred