2

I have to download binary files stored in PostgreSQL database as bytea and then work with them in R. I use DBI library to download the content

data <- dbGetQuery("select binary_content from some_table limit 1", connection)

next I have to work with this content. The problem is that even after reviewing SO threads (e.g. this one), PostgreSQL documentation, R documentation for several functions (writeBin, charToRaw, as.raw etc.), multiple web pages and intensive Googling I am unable to find any hints how it can be done in R. What I want to do is to (1) download the content, (2) save it locally as individual files, (3) work with the files. No matter what I do R always saves the content as it was a long gibberish character string.

Unfortunately I am unable to provide reproducible example since I cannot share the data I am using.

Tim
  • 7,075
  • 6
  • 29
  • 58
  • 1
    If you are querying out binary content, then you will have to either save it as binary, or use a library to convert it. If you can't share with us any information about what this binary represents, then don't expect a useful answer here. – Tim Biegeleisen Jan 18 '16 at 10:21
  • @TimBiegeleisen the question is how to do it and where to find information about it, as for now I am unable to read or write it. – Tim Jan 18 '16 at 10:22
  • You just told us that R `saves the content as ... a long gibberish` string. This sounds like a binary blob. So the problem isn't R, it is your decision about how to handle binary content. – Tim Biegeleisen Jan 18 '16 at 10:23
  • @TimBiegeleisen I am trying a number of different combinations how to encode the output in postgresql (http://www.postgresql.org/docs/9.3/static/functions-binarystring.html) and how to write it from R but neither of them works. The content is CSV files and I'd like just to save them and work with them as with CSVs. – Tim Jan 18 '16 at 10:29
  • Let's take a step back here. What is this content, and how was it originally written into your Postgres database? This question is more critical than the one you actually asked. – Tim Biegeleisen Jan 18 '16 at 10:31
  • @TimBiegeleisen It is csv file written as `bytea` column. – Tim Jan 18 '16 at 10:42
  • How is it being encoded? – Tim Biegeleisen Jan 18 '16 at 10:44
  • How this gibberish string looks like? Is it made just of numbers in the 0-7 range? Or by 0-9a-f characters? Is it arbitrary? Please, show us an example (maybe just reorder the characters of your actual data). – nicola Jan 18 '16 at 10:59
  • @TimBiegeleisen the only thing I know that it is ASCII bytea – Tim Jan 18 '16 at 13:19
  • 1
    Please update your question by showing a sample of the binary ASCII you have. In the meantime, have a look at [this site](https://www.base64decode.org/) where you can decode base 64 online. This is my hunch about what you have. – Tim Biegeleisen Jan 18 '16 at 13:36
  • the bytea is read into R as a hex encoded character string. You have to convert it. See answer here: https://stackoverflow.com/a/50999966/2371031 – Brian D Dec 06 '18 at 15:07

0 Answers0