Re: interpret bytea output as text / double encode()

Sent from my iPhone

> On Jun 6, 2014, at 7:00 AM, Stefan Froehlich <postgresql(at)froehlich(dot)priv(dot)at> wrote:
>
> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Any chance you can encode() into temp table then encode() the result.