Menu
I understand that PostgreSQL writes BLOB content to a separate table, but is there any way to view the blob contents in an easy and convenient way from inside pgAdmin?
424
To view or modify data, right click on a table or view name in the Browser tree control. When the context menu opens, use the View/Edit Data menu to specify the number of rows you would like to display in the editor panel. To modify the content of a table, each row in the table must be uniquely identifiable.
“Blob” stands for “binary large object” and refers to raw binary data stored in a database. Blobs can be images, audios, or other large file formats. Databases handle blobs differently, and here we will see an example of how an image is processed in PostgreSQL and PHP.
PostgreSQL blob data type is defined as binary large object, basically blob data type is not available in PostgreSQL instead of blob we have using bytea data type. Blob data type in PostgreSQL is basically used to store the binary data such as content of file in PostgreSQL.
PostgreSQL does not support BLOB but you can use the BYTEA data type for storing the binary data.
SELECT encode(blobdata::bytea, 'escape') FROM table as o where o.blobdata != ''
where
93
If we need SQL operations through SQL Clients (like pgAdmin) on binary columns, it would be better to use base64 encoding as below
To fetch binary data in base64 format
select id, encode(blob_column::bytea, 'base64') as blob_column from blob_table where id=1;
To update binary data by providing base64 formatted data
update blob_table set blob_column = decode('J0u0v1h4CulinCwUvk4dhw==', 'base64') where id=1;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
