Subject: Bytea datatype content to view

From: Wasim Devale <[email-masked]>
Date: Wed, 6 Nov 2024 18:35:30 +0530

Hi All

I have a table having bytea datatype. Anyone has worked on it how we can
see the content as the files are in .csv, .pdf, .bin and .docx format.

Can anyone help with this? Any SQL script or python script to view the
content?

Thanks,
Wasim

---

From: Steve Cooper <[email-masked]>
Date: Wed, 6 Nov 2024 06:09:09 -0800

A PostgreSQL bytea type is the hexadecimal representation of the ASCII
codes for a character string.

So, the string 'hex' translates to \x686578 in a bytea field.
\x68 = decimal 104 = 'h'; \x65 = decimal 101 = 'e'; \x78 = decimal 120 = 'x'

Here's a short Python function to translate hex ascii to a character string:

def hexToStr(hexString: str) -> str:
    # Convert hex string to bytes
    bytesObject: bytes = bytes.fromhex(hexString)
    # Decode bytes to string
    asciiString: str = bytesObject.decode("ASCII")
    return asciiString

if __name__ == "__main__":
  hex_string = "686578"  # hexadecimal representation of the ASCII codes
for the string 'hex'
  print(hexToStr(hex_string))

# output: hex

Rewrite the above "__main__" part to open a database, select whatever, loop
through the results, translate the field(s) in question, and perform
whatever output you like.

---

From: Holger Jakobs <[email-masked]>
Date: Wed, 6 Nov 2024 15:09:58 +0100

Am 06.11.24 um 14:05 schrieb Wasim Devale:
> Hi All
>
> I have a table having bytea datatype. Anyone has worked on it how we 
> can see the content as the files are in .csv, .pdf, .bin and .docx format.
>

Hi Wassim,

You just have to take the contents of the bytea field, which usually is 
delivered by the query in pairs of hex digits, and write it to a file, 
which you then can open. The writing has to take place 8-bit clean, in 
binary.

In order to write a file, you must provide a file name, so somewhere 
else you have to get the name from, esp. the file extension.

If you don't have this information, you might use the file command in 
Linux to determine the type of content. The file command reads the first 
few bytes of a file and determines the type from the "magic number" 
found there.

Storing a binary file including its MIME type, just as attachments in 
mails are, can be easier in handling. The MIME-encoded binary file is 
text, so you can easily store it to text colums.

Big files belong into the file system though, not into the database as 
they make the database large and clumsy.

Kind Regards,
Holger

-- 

Holger Jakobs, Bergisch Gladbach

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 6 Nov 2024 17:03:00 +0100

> see the content as the files are in .csv, .pdf, .bin and .docx format.

In a pinch, you could turn the bytea column into an LO using the
lo_from_bytea() function and use psql's \lo_export.  Quick and easy, no
need to write any code.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/

---

From: Wasim Devale <[email-masked]>
Date: Thu, 7 Nov 2024 14:38:05 +0530

Thanks everyone for your valuable inputs.