[postgis-users] Bytea data type and its application and uses

[Shaozhong SHI]

We can see various publications on the Bytea data type.

However, it is difficult to find practical examples of how to use it.

Anyone knows?

Regards,

Shao

[Giuseppe Broccolo]

Hi Shao,

What do you need to store, more specifically? Since this is a PostGIS related thread, is it about raster data, etc.?

Anyway, as a general info about binary data: there are two general ways to insert binaries into a PostgreSQL database, the hex format and the escape format. I attach here the reference: https://www.postgresql.org/docs/12/datatype-binary.html

As a personal experience, I used the hexadecimal format: the idea is to convert the binary blob into hex digits representation, where one digit represents an hex nibble, and each byte of the binary blob is converted into two digits, with the most significant nibble first. Then the hex digits can be inserted as you would insert a string, preceded with the char  \x.

Depending on what you use at the application side, there are different ways of encoding a binary blob into hex format. If you want to try something "manually" from a file using psql, try with utilities like hexdump, keeping in mind that the binary content in the file is encoded using the platform's endianness, i.e. with the most significant nibble put for first or for last (check this from your OS).

Hope it helped,

Giuseppe.

_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[Shaozhong SHI]

Hi,  Giuseppe,

Thank you for your reply.

We know those literature.  The issue is that there is a lack of working examples to demonstrate its use.

Let's say that people are using PowerBI, Graphic Packages, GIS application users and etc.  Can these packages access and retrieve image data (Bytea) from PostGIS?

Regards,

Shao

[Giuseppe Broccolo]

Hi Shao,

I don't know how PowerBI works frankly. My idea is that they do not support direct access to bytea data, so maybe you need to put something in the middle to decode/encode.

Giuseppe.