Обсуждение: How to transfer binary data into a BIT VARYING column ?

Hello

I have a BIT VARYING(83886080) column in my table and I would like to
store file attachments
in it.

Since the files to attach can be arbitrary large I tried to use a
query parameter of type
large object to insert values in the BIT VARYING column, but psqlODBC
first complained
that there is no 'lo' type defined, and after creating the type it
complained that type 'lo' is
different than type 'BIT VARYING'.

I would like to use BIT VARYING because it is an ANSI-conformant
binary type, while
bytea and 'lo' are not ANSI SQL.

I noticed I can insert a string of characters (not bits) '0' and '1'
in the BIT VARYING column
and PostgreSQL will convert them to bits, but I would like to use
large objects because such
a string would be 8 times larger in size than the file, and it can not
be streamed, I need the
entire string as a parameter to run the INSERT statement.

My ODBC client (php with PDO and PDO_ODBC modules) allows to stream large object
data so it can be sent to the ODBC while it is read from the disk,
without the need to load
it all in memory.

Is there a way to do this ?

I mean the driver can store/retrieve large objects, I just want them
stored in a BIT VARYING
column.

Can the driver use large object data with a BIT VARYING column somehow
? Is there a
cast or a trick I could do ? Or a patch or development version that
can do this ?


Thank you,
Timothy Madden

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.

            regards, tom lane

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