Обсуждение: decoding BLOB's

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?

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.

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.

-- 
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.