Postgres BYTEA encoding and JSONB output

Question

I have built an API on top of Postgres that is routed through a PLPGSQL function that always returns JSONB. eg:-

SELECT api('{"method", "foo"}'::JSONB)

Returns:-

{
 "response_code" : 200,
 "data" : "...."
}

I want to use this gateway to render vector tiles which are in PBF format. I have a function create_vector_tile(x,y,z) which returns BYTEA and this works perfectly if I connect a python script to it directly:-

query = DATABASE_CONNECTION.execute(create_vector_tile(%,%,%), [x,y,z])
ret = query.fetchone()[0]
self.send_response(200)
self.send_header("Access-Control-Allow-Origin", "*")
self.send_header("Content-type", "application/vnd.mapbox-vector-tile")
self.end_headers()
self.wfile.write(ret)

How do I decode this bytea if I return it via my API eg:-

RETURN json_build_object('response_code', 200, 'data', create_vector_tile(x,y,z))

Postgres automatically encodes the bytea as a string in the JSON object but I don't know how to decode this in python to get the same result as the direct call. I need to go via my API as it does authentication and logging.

\x1aa7360a0764656661756c7412820412e10300000101020203030404050506060707080809090a030b0a0c0b0d0c0e0d0f (first 100 characters) — CitizenFish
– CitizenFish, Commented Jul 29, 2022 at 10:06
You can return the binary data in a different format if you want (e.g. base64) using the encode() function — user330315
– user330315, Commented Jul 29, 2022 at 11:20

snakecharmerb · Accepted Answer · 2022-07-29 10:18:49Z

1

Postgresql's BYTEA is a hex string preceded by \x. Assuming it arrives with the leading backslash properly escaped it can be converted to bytes using bytes.fromhex:

>>> bytea = '\\x1aa7360a0764656661756c7412820412e10300000101020203030404050506060707080809090a030b0a0c0b0d0c0e0d0f'
>>> bs = bytes.fromhex(bytea[2:])
>>> bs
b'\x1a\xa76\n\x07default\x12\x82\x04\x12\xe1\x03\x00\x00\x01\x01\x02\x02\x03\x03\x04\x04\x05\x05\x06\x06\x07\x07\x08\x08\t\t\n\x03\x0b\n\x0c\x0b\r\x0c\x0e\r\x0f'

If the backslash is not escaped, this will work:

>>> bytea = '\x1aa7360a0764656661756c7412820412e10300000101020203030404050506060707080809090a030b0a0c0b0d0c0e0d0f'
>>> escaped = bytea.encode('unicode-escape').decode('ascii')
>>> bytes.fromhex(escaped[2:])
b'\x1a\xa76\n\x07default\x12\x82\x04\x12\xe1\x03\x00\x00\x01\x01\x02\x02\x03\x03\x04\x04\x05\x05\x06\x06\x07\x07\x08\x08\t\t\n\x03\x0b\n\x0c\x0b\r\x0c\x0e\r\x0f'

answered Jul 29, 2022 at 10:18

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CitizenFish Over a year ago

The first option works perfectly. Thanks

Collectives™ on Stack Overflow

Postgres BYTEA encoding and JSONB output

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related