Thread: Bytea datatype content to view
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
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
# 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.
On Wed, Nov 6, 2024 at 5:05 AM Wasim Devale <wasimd60@gmail.com> wrote:
Hi AllI 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
On 2024-Nov-06, Wasim Devale wrote: > 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. 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/
Thanks everyone for your valuable inputs.
On Wed, 6 Nov, 2024, 9:33 pm Alvaro Herrera, <alvherre@alvh.no-ip.org> wrote:
On 2024-Nov-06, Wasim Devale wrote:
> 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.
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/