0

So, I've got some generic data in a file. My quest is simple: store that in a field in the database, and retrieve it again later back in to a file. I don't care how it is stored, nor what methods used to transfer it back and forth (but if i can contain it to a plpgsql function or two, that would be an awesome bonus). But it must come back to a file exactly the same as it was entered, bit for bit. I cannot guarantee that it wouldn't contain any special characters, and the data varies in size from a few KB to a few MB.

I've been trying with COPY, lo_import and lo_export, pg_read_file(etc) and every time, the resulting file is missing certain characters (encoding issues?) or is a different representation of what may be the same file (\000\014 etc from bytea) or is practically garbage. I'm comparing the input/output in notepad++ and also running the output each time but to no avail.

There's PHP, C++(+boost[speaking of which, i tried binarystrings too...]) and this database sharing a 'working directory' that i've been using to transfer certain things between programs so any of these could do the job if necesary, but, as mentioned above, i'd like to use a plpgsql function or two if possible.

Also, please explain and example the solution like you're talking to an idiot. I certainly feel like one after having this problem for too long.

Thank you loads in advance, you will be solving much headache.

IamPancakeMan
  • 199
  • 1
  • 10
  • This can be helpful: [Storing images in bytea fields in a PostgreSQL database](http://stackoverflow.com/questions/17121791/storing-images-in-bytea-fields-in-a-postgresql-database). – klin Jun 26 '15 at 21:25
  • As far as I know, `lo_import` and `lo_export` are the only facilities provided by Postgres for binary file I/O. Following the example in the [documentation](http://www.postgresql.org/docs/9.3/static/lo-funcs.html), I get an output file which is byte-for-byte identical to the input. Could you show us exactly how you called these functions, with an example of your input/output? – Nick Barnes Jun 27 '15 at 04:30
  • `lo_import` and `lo_export` arn't solutions i managed to get working actually. I have been reading on it and i get the idea that it's stored elseware and and OID is just a reference and stuff. I import the data from file to bytea table using lo_import and other lines, which i pulled from another online answer on SO. I know it works because the data in the table is the same as that before, in the file. Getting it back out appears to be problematic, however. Again, i have been reading up on it but looking for an example to work with yields no results. – IamPancakeMan Jun 27 '15 at 10:58
  • 1
    @IamPancakeMan: If all you are trying to do is load and save files, then then there is no need to convert them to `bytea`. You can do this with the OIDs alone, as demonstrated in the [documentation](http://www.postgresql.org/docs/9.3/static/lo-funcs.html). – Nick Barnes Jun 27 '15 at 11:36
  • 1
    i don't know why my colleague suggested to use a bytea column for this, using OIDs took 10 minutes to understand and implement. Thank you very much for your assistance kind sir. – IamPancakeMan Jun 27 '15 at 22:38
  • You're welcome. `bytea` is the obvious choice for storing binary data in most situations; it just happens to lack the same support for working with server-side files. Just in case you missed it, don't forget to `lo_unlink` the OIDs when you're done with them ;) – Nick Barnes Jun 27 '15 at 23:49

0 Answers0