Jason,
BLOBs as you have correctly inferred do not get automatically deleted.
You can add triggers to your tables to delete them automatically if you
so desire.
However 'bytea' is the datatype that is most appropriate for your needs. It has been around for a long time, but not
welldocumented. I have
been using it in my code since 7.0 of postgres and it works fine. In
fact many of the internal postgres tables use it.
The problem with bytea is that many of the client interfaces don't
support it well or at all. So depending on how you intend to access the
data you may not be able to use the bytea datatype. The situation is
much improved in 7.2 with bytea documented and better support for it in
the client interfaces (jdbc especially).
Encoding the data into a text format will certainly work, if you can't
work around the current limitations of the above two options. And I
believe there is some contrib code to help in this area.
thanks,
--Barry
Jason Orendorff wrote:
> Reply-To: sender
>
> Hi. I was surprised to discover today that postgres's
> character types don't support zero bytes. That is,
> Postgres isn't 8-bit clean. Why is that?
>
> More to the point, I need to store about 1k bytes per row
> of varying-length 8-bit binary data. I have a few options:
>
> + BLOBs. PostgreSQL BLOBs make me nervous. I worry about
> the BLOB not being deleted when the corresponding row in
> the table is deleted. The documentation is vague.
>
> + What I really need is a binary *short* object type.
> I have heard rumors of a legendary "bytea" type that might
> help me, but it doesn't appear to be documented anywhere,
> so I hesitate to use it.
>
> + I can base64-encode the data and store it in a "text"
> field. But postgres is a great big data-storage system;
> surely it can store binary data without resorting to
> this kind of hack.
>
> What should I do? Please help. Thanks!
>
>