Re: How to transfer binary data into a BIT VARYING column ?

On Tue, May 19, 2009 at 6:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Timothy Madden <terminatorul@gmail.com> writes:
>> I have a BIT VARYING(83886080) column in my table and I would like to
>> store file attachments
>> in it.
>
> Use bytea.
>
>> I would like to use BIT VARYING because it is an ANSI-conformant
>> binary type, while bytea and 'lo' are not ANSI SQL.
>
> Considering the nonstandard things you will have to do to get data into
> the column, this seems like a pretty silly argument.
>

Thank you.

My last resort would be to use repeated

      UPDATE
          "Messages"
      SET
          "AttachmentsData" =
                     "AttachmentsData"
                           ||
                    CAST( '01001001010'   AS   BIT VARYING(83000000) );

statements to append string segments of data into the column until I
get all the file pushed into the BIT VARYING column. In a way it is
like trying to stream the data into the column myself, while using
only strings for input from the client.

My main concern here is how PostgreSQL 8.3 server impements
concatenation for bit strings internally. It would be interesting if
the server would understand that all that is needed is to append to
the column's value, and not try to replace it entirely with a new,
larger value. I also hope its storage doesn't get fragmented to much
or some other such thing by this approach and I still have to check
the SQL-3 standard if this string to varbit conversion is defined.
Would VACUUM help compensate fragmentation problems if needed in such
cases ?

If this works, than it is pretty standard (excluding SQL 2003 and
later that seem to have dropped BIT VARYING) and I have no memory
problems or limitations for php strings length, but still it is a poor
man's way of doing things.

Thank you,
Timothy Madden