Thread: 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