Thread: decoding BLOB's
Hi All;
we've found a post about dumping blobs:
Can someone point me in the right direction per how I would remove the first 25 bytes and the last 2 bytes from a bytea column?
Thanks in advance
we've found a post about dumping blobs:
I wrote: [fanlijing wants to write bytea to file] > A simple > COPY (SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT binary) > should do the trick. Corrections: a) "binary" must be surrounded by single quotes. b) that won't dump just the binary data - you would have to remove the first 25 bytes and the last 2 bytes... So maybe using the functions I mentioned would be the best way after all. You could also write your own user defined function in C.
Can someone point me in the right direction per how I would remove the first 25 bytes and the last 2 bytes from a bytea column?
Thanks in advance
On 02/22/2016 11:08 AM, CS DBA wrote: > Hi All; > > we've found a post about dumping blobs: > / > / > > /I wrote: [fanlijing wants to write bytea to file] > A simple > COPY > (SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT binary) > > should do the trick. Corrections: a) "binary" must be surrounded by > single quotes. b) *that won't dump just the binary data - you would have > to remove the first 25 bytes and the last 2 bytes...* So maybe using the > functions I mentioned would be the best way after all. You could also > write your own user defined function in C. / > > > > Can someone point me in the right direction per how I would remove the > first 25 bytes and the last 2 bytes from a bytea column? http://www.postgresql.org/docs/9.3/static/functions-binarystring.html Substring might do it for you. JD > > Thanks in advance > > -- Command Prompt, Inc. http://the.postgres.company/ +1-503-667-4564 PostgreSQL Centered full stack support, consulting and development. Everyone appreciates your honesty, until you are honest with them.
On 2/22/2016 11:23 AM, Joshua D. Drake wrote:
Can someone point me in the right direction per how I would remove the
first 25 bytes and the last 2 bytes from a bytea column?
http://www.postgresql.org/docs/9.3/static/functions-binarystring.html
Substring might do it for you.
won't doing it in SQL still result in a BYTEA result which will be wrapped when dumped via COPY ?
instead, I think this needs to be done externally to sql, like by piping the resulting file through something like ...
tail -c +25 infile.dat | head -c -2 > outfile.dator doing something in perl or whatever.
-- john r pierce, recycling bits in santa cruz
>>> Can someone point me in the right direction per how I would remove the >>> first 25 bytes and the last 2 bytes from a bytea column? >> >> http://www.postgresql.org/docs/9.3/static/functions-binarystring.html >> >> Substring might do it for you. > > won't doing it in SQL still result in a BYTEA result which will be wrapped when dumped via COPY ? > > instead, I think this needs to be done externally to sql, like by piping the resulting file through something like ... > > tail -c +25 infile.dat | head -c -2 > outfile.dat > > or doing something in perl or whatever. Hi, I think it's easier if you go via base64 and then use the shell command "base64" to decode it back to binary. I start from this sample blob with 5 bytes (values 0, 1, 2, 4, 5), just a sample (I actually failed to count to 4 correctly ;): chris=# select * from t; id | blob ----+-------------- 1 | \x0001020405 (1 row) In the shell now I can do: chris$ psql -A -t -c "select encode(blob, 'base64') from t where id = 1" AAECBAU= To save this in binary I add base64 -d (Linux) or base64 -D (OS X, FreeBSD?): chris$ psql -A -t -c "select encode(blob, 'base64') from t where id = 1" | base64 -D > blob.dat blob.dat is now the 5 bytes, nothing else: chris$ od -tx1 blob.dat 0000000 00 01 02 04 05 0000005 Bye, Chris.