Update bytea data that was first encoded as a hex char string-CodePudding

Given a table with a "blob" column of bytea type, that has data that was first encoded as a char string of 'hex' format by the toString method of Node's Buffer API ... yes, not the best idea ...

Is it possible to update the data so that the data is decoded from 'hex' and returned to raw bytes?

decode(blob,'hex') is not going to work as the blob is still bytea, not text.

Looking for a possibly 'pure' Postgres (> v12) solution without going back to Node's Buffer API first, but I'll accept the punishment of having to export the data, transform it, and update from there.

CodePudding user response：

Fear not:

UPDATE blobs
SET b = decode(encode(b, 'escape'), 'hex');