public inbox for [email protected]help / color / mirror / Atom feed
Bytea datatype content to view 5+ messages / 4 participants [nested] [flat]
* Bytea datatype content to view @ 2024-11-06 13:05 Wasim Devale <[email protected]> 0 siblings, 3 replies; 5+ messages in thread From: Wasim Devale @ 2024-11-06 13:05 UTC (permalink / raw) To: pgsql-admin; Pgsql-admin <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bytea datatype content to view @ 2024-11-06 14:09 Steve Cooper <[email protected]> parent: Wasim Devale <[email protected]> 2 siblings, 0 replies; 5+ messages in thread From: Steve Cooper @ 2024-11-06 14:09 UTC (permalink / raw) To: Wasim Devale <[email protected]>; +Cc: pgsql-admin; Pgsql-admin <[email protected]> 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. On Wed, Nov 6, 2024 at 5:05 AM Wasim Devale <[email protected]> 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. > > Can anyone help with this? Any SQL script or python script to view the > content? > > Thanks, > Wasim > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bytea datatype content to view @ 2024-11-06 14:09 Holger Jakobs <[email protected]> parent: Wasim Devale <[email protected]> 2 siblings, 0 replies; 5+ messages in thread From: Holger Jakobs @ 2024-11-06 14:09 UTC (permalink / raw) To: [email protected] 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. > > Can anyone help with this? Any SQL script or python script to view the > content? > > Thanks, > Wasim 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 Attachments: [application/pgp-signature] OpenPGP_signature (203B, 2-OpenPGP_signature) download ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bytea datatype content to view @ 2024-11-06 16:03 Alvaro Herrera <[email protected]> parent: Wasim Devale <[email protected]> 2 siblings, 1 reply; 5+ messages in thread From: Alvaro Herrera @ 2024-11-06 16:03 UTC (permalink / raw) To: Wasim Devale <[email protected]>; +Cc: pgsql-admin; Pgsql-admin <[email protected]> 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/ ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bytea datatype content to view @ 2024-11-07 09:08 Wasim Devale <[email protected]> parent: Alvaro Herrera <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Wasim Devale @ 2024-11-07 09:08 UTC (permalink / raw) To: Alvaro Herrera <[email protected]>; +Cc: pgsql-admin; Pgsql-admin <[email protected]> Thanks everyone for your valuable inputs. On Wed, 6 Nov, 2024, 9:33 pm Alvaro Herrera, <[email protected]> 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/ > ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-11-07 09:08 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-11-06 13:05 Bytea datatype content to view Wasim Devale <[email protected]> 2024-11-06 14:09 ` Steve Cooper <[email protected]> 2024-11-06 14:09 ` Holger Jakobs <[email protected]> 2024-11-06 16:03 ` Alvaro Herrera <[email protected]> 2024-11-07 09:08 ` Wasim Devale <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox