PostgreSQL BLOB Tools - Import, Export, View and Edit

• Import file data to PostgreSQL BYTEA (BLOB)
• Export PostgreSQL BYTEA (BLOB) data to files
• View and edit BLOB data stored in PostgreSQL
• View and edit image stored in PostgreSQL BYTEA field
• View PDF stored in PostgreSQL BYTEA field
• Batch export PostgreSQL query results BYTEA data to files
• Batch update PostgreSQL table BYTEA data from files
• Batch insert PostgreSQL table BYTEA data from files

PostgreSQL BLOB - type family and size limits

Type in PostgreSQL	Maximum size
BYTEA	up to 1 GB - the usual choice
OID + large object	up to 4 TB, read and written with lo_import / lo_export

BLOB versus CLOB in PostgreSQL

PostgreSQL has no BLOB or CLOB type names either: binary goes in `BYTEA` and characters go in `TEXT`. Both reach 1 GB. Only if you genuinely need multi-terabyte objects should you turn to OID-based large objects.

What limits you first

TOAST usually bites first: any value over roughly 2 KB is compressed and moved into a side table. The 1 GB type limit is rarely reached, but TOAST has a visible cost in read performance and backup size.

BLOB from the command line

• Export: pg_dump -Fc (custom format restores bytea correctly) or COPY ... TO ... WITH (FORMAT binary)
• Import: pg_restore, or COPY ... FROM ... WITH (FORMAT binary)

Things that bite with BLOB in PostgreSQL

• A plain-text pg_dump writes bytea as \x... hex literals, so the dump balloons - use -Fc when moving data between servers
• COPY ... WITH (FORMAT binary) records the platform byte order in the file header, so restoring on a different architecture fails
• The \x hex input/output form for BYTEA relies on standard_conforming_strings = on, which is the modern default
• TOAST compression helps large text but does almost nothing for already-compressed binary such as images or zip archives, while still costing CPU