In one of my databases, I have mistakenly double encoded bytea
values (i.e. the content is literally '\x202020...' which would have
to be decoded once more to get the actually desired content).
But how to get to the content? This:
# select encode(column, 'escape')
gives me the once-only encoded value, which I'd like to decode once more
to get the original content. Using this:
# select encode(encode(column, 'escape'), 'escape')
does not work, because PostgreSQL is aware of the fact, that the result
of the inner operation is text and thus may not be used as an argument
for encod(). Casting the result does not work either:
# select encode(encode(column, 'escape')::bytea, 'escape')
because the cast reverts the effect of the first encode(), so the result
does not change.
If I take the result of the first encode() with copy/paste and put
it through another encode() statement, everything is fine - but I
have not found out a way how to automate this.
Is there a chance? Can anyone help out?
Stefan