How can I convert an escaped string to a bytea in postgresql (\ is not a binary digit) with Azure Data Studio

Question

This is probably a duplicate but I've been unable after quite some time to actually find out how to do this.

What I'm trying to do is simply roundtrip a binary value. I select it, then paste it into another SQL query.

I.e.

• I have a table with columns id, data
• I run this query in Azure Data Studio with the postgres extension: select id, data from mytable
• The output window shows this

id  data
58  b'\xaa\xb6\x87|-x\xf6\xd1\x80\xec\xd2\xed\x91\x1ebd\x924g\xcd\xaeVZ\x01\xf2\xa0\xb3\xd8\x16\xe1fC'

Now I'm trying to do a second query where I count things matching that one.

select count(*) from mytable where data = b'\xaa\xb6\x87|-x\xf6\xd1\x80\xec\xd2\xed\x91\x1ebd\x924g\xcd\xaeVZ\x01\xf2\xa0\xb3\xd8\x16\xe1fC'

And I get the error message "\" is not a valid binary digit

Most of the docs and other things I've found seem to suggest the E'...'::bytea syntax, so I do this:

select count(*) from mytable where data = E'\xaa\xb6\x87|-x\xf6\xd1\x80\xec\xd2\xed\x91\x1ebd\x924g\xcd\xaeVZ\x01\xf2\xa0\xb3\xd8\x16\xe1fC'::bytea

And I get the error message invalid byte sequence for encoding "UTF8": 0xaa

I've tried the decode function as other things suggested that:

select count(*) from mytable where data = decode('\xaa\xb6\x87|-x\xf6\xd1\x80\xec\xd2\xed\x91\x1ebd\x924g\xcd\xaeVZ\x01\xf2\xa0\xb3\xd8\x16\xe1fC', 'escape')

And that fails with the error invalid input syntax for type bytea

Why would the output format for binary data be something which is not a valid input format? Hopefully this is not just Azure Data Studio being strange :-(

Answer 1

When using hex values with decode() you need to use the hex option and provide only valid hex numbers:

decode('aab687', 'hex')

Alternatively you can prefix the hex values with a single \x, e.g.

'\xaab687'::bytea

However your string contains invalid hex values e.g. \x1ebd, \x924g and \x87|-x - not sure what the are supposed to be.

The "escape" syntax (e.g. using E'...') requires octal values, not hex numbers, so E'\xaa\xb6... should be E'\\252\\266.... The same is true when using the 'escape' option for decode()

Answer 2

This turns out to be Azure Data studio's behaviour, and not specific to postgresql.

When I run the same query using psql I get a sensibly formatted hex string which works fine in roundtrip queries, that looks like \xbc6b702a12f8adb482ad517eca5af6407c9f172091242aa79845a17b4d479e2d (note different data value but the format is correct)

I filed an issue against the postgresql addin for Azure Data Studio and hopefully it can be resolved that way.

Issue Link: https://github.com/microsoft/azuredatastudio-postgresql/issues/204

As a workaround I will need to use select encode(column, 'hex') in all my queries