PostgreSQL CLOB Tools - Import, Export, View and Edit

• Import file data to PostgreSQL TEXT (CLOB)
• Export PostgreSQL TEXT (CLOB) data to files
• View and edit PostgreSQL TEXT data
• Batch export PostgreSQL query results TEXT data to files
• Batch update PostgreSQL table TEXT data from files
• Batch insert PostgreSQL table TEXT data from files

PostgreSQL CLOB - type family and size limits

Type in PostgreSQL	Maximum size
TEXT	up to 1 GB, no length argument, recommended
VARCHAR(n)	length-limited; an overlong value raises an error instead of truncating
CHAR(n)	blank-padded fixed width, rarely what you want

CLOB versus BLOB 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.

CLOB 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 CLOB 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