Store RTF in PostgreSQL TEXT

by

Posted on by

RTF(Rich Text Format) can be stored as PostgreSQL TEXT data.

1. PostgreSQL TEXT Type Overview

PostgreSQL TEXT has no character length limits (disk-bound) and natively supports large character storage.

Compatible with VARCHAR syntax, no special initialization required, extremely simple to operate.

2. Pure SQL Implementation Steps

Step 1: Create Table with TEXT Field

CREATE TABLE RTF_DOCS (
    DOC_ID SERIAL PRIMARY KEY, -- Auto-increment primary key
    DOC_NAME VARCHAR(100) NOT NULL,
    RTF_CONTENT TEXT NOT NULL, -- Core field for RTF storage
    CREATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert RTF Text into TEXT Field

-- Method 1: Insert short RTF text directly
INSERT INTO RTF_DOCS (DOC_NAME, RTF_CONTENT)
VALUES (
    'test.rtf',
    '{\\rtf1\\ansi\\deff0 {\\fonttbl {\\f0 Calibri;}} \\pard\\f0\\fs22 PostgreSQL RTF Test\\par}'
);

-- Method 2: Import external RTF file (psql command line)
-- 1. Log in to psql: psql -U username -d your_database
-- 2. Import file content with the following commands
\set rtf_content `cat /path/to/test.rtf` -- Read local RTF file
INSERT INTO RTF_DOCS (DOC_NAME, RTF_CONTENT) VALUES ('imported.rtf', :'rtf_content');

Step 3: Query/Edit RTF Content

-- Query full RTF content
SELECT DOC_NAME, RTF_CONTENT FROM RTF_DOCS WHERE DOC_ID = 1;

-- Truncate RTF preview (first 180 characters)
SELECT DOC_NAME, SUBSTRING(RTF_CONTENT FROM 1 FOR 180) AS RTF_PREVIEW FROM RTF_DOCS;

-- Update RTF content
UPDATE RTF_DOCS 
SET RTF_CONTENT = '{\\rtf1\\ansi\\deff0 \\pard\\f0\\fs28 Updated PostgreSQL RTF\\par}'
WHERE DOC_ID = 1;

Step 4: Delete RTF Record

DELETE FROM RTF_DOCS WHERE DOC_ID = 2;

3. Simplify PostgreSQL TEXT RTF Management with DBBlobEditor

DBBlobEditor optimizes RTF handling for PostgreSQL TEXT fields:

4. Notes

  • Ensure files are UTF-8 encoded when reading with psql’s \set command to avoid garbled text
  • PostgreSQL TEXT fields support full-text search; create GIN indexes to optimize RTF content queries
  • No character length limits, but keep single records under 1GB to avoid performance issues

Related Guides