What is the encoding used in encode function with escape in postgres?-CodePudding

I've a bytea stored in the database , I'm trying to convert it to string via the encode function and it works :

select encode(blobvalue,'escape') from myTable ;

However the following is failing :

select convert_from(blobvalue,'UTF8') from myTable ;

ERROR:  invalid byte sequence for encoding "UTF8": 0xac

my server encoding is UTF8:

SHOW SERVER_ENCODING;
 server_encoding
-----------------
 UTF8
(1 row)

any explanation why the encode is working but the convert_from not ? isn't the encoding used in encode with escape same as server encoding ?

CodePudding user response：

encode() will only convert ASCII bytes to characters. Everything else will be an escaped octal value:

SELECT encode('\x4142AC43', 'escape');

 encode  
═════════
 AB\254C
(1 row)

Here, the byte 0xAC is not an ASCII character and is rendered as \254.

convert_from(), on the other hand, considers the bytea as a string in a certain encoding and will fail if it finds bytes that are not correct:

SELECT convert_from('\x4142AC43', 'UTF8');
ERROR:  invalid byte sequence for encoding "UTF8": 0xac

So you have to figure out in which encoding your bytea value actually is.