Shell script to dump contents of bytea column to a file

Please note that bytea columns, in pg 9.0 and above, display as hex, with an irritating leading '\x', which can be removed using pg's substring funciton.


set -e
set -u

psql \
    -P t \
    -P format=unaligned \
    -X \
    -U myuser \
    -h myhost \
    -c "select substring(my_bytea_col::text from 3) from my_table where id = 12" \
    mydb \
| xxd -r -p > dump.txt