CODEWITHSUNDEEP
postgresqlpostgresql-12
sqldoug
sqldoug

Reputation: 419

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

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.

Upvotes: 0

Views: 914

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247790

Fear not:

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

With the escape format, the hex digits you stored by mistake will be output as characters, and the hex format will interpret pairs of these hex digits as a single byte.

Upvotes: 2

Related Questions