Definition
- Oracle CLOB – a Character Large OBject storing up to about 4 GB, accessed through a LOB locator and
DBMS_LOBprocedures. - PostgreSQL TEXT – a native variable-length character type with no declared length limit (up to about 1 GB per value), used like any ordinary column.
- Same underlying type – in PostgreSQL,
VARCHAR(n),VARCHAR, andTEXTare all the same storage; the only difference is the optional length check. - TOAST – PostgreSQL automatically moves large values out-of-line into TOAST storage and compresses them transparently.
- Migration mapping –
CLOB→TEXT,NCLOB→TEXT,VARCHAR2→VARCHARorTEXT,BLOB→BYTEA.
Imagine National Bank of India storing a customer's long complaint letter. In Oracle you put it in a special oversized drawer called a CLOB that needs its own handling procedure. In PostgreSQL you use a TEXT column: the same drawer you use for a short name works for a whole book, with no special drawer to manage.
Understanding the Concepts
What is Oracle's CLOB type?
Oracle distinguishes small strings (VARCHAR2, capped at 4000 bytes) from large character data (CLOB, up to about 4 GB). A CLOB is accessed through a LOB locator and older code uses DBMS_LOB procedures.
-- Oracle: CLOB for large text
CREATE TABLE complaints (
id NUMBER PRIMARY KEY,
customer_id NUMBER NOT NULL,
narrative CLOB -- up to 4 GB
);
-- Accessing CLOB in Oracle requires LOB procedures:
-- DBMS_LOB.GETLENGTH(narrative)
-- DBMS_LOB.SUBSTR(narrative, 1000, 1)
What is PostgreSQL's TEXT type?
PostgreSQL's TEXT is a native variable-length string with no declared length and a practical ceiling of about 1 GB.
-- PostgreSQL: TEXT for any length of character data
CREATE TABLE complaints (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL,
narrative TEXT -- up to ~1 GB, no special API needed
);
-- Read and write like any normal column
INSERT INTO complaints (customer_id, narrative)
VALUES (1001, 'Long complaint text about account issues at National Bank...');
SELECT LENGTH(narrative), SUBSTRING(narrative, 1, 100)
FROM complaints
WHERE customer_id = 1001;
How are VARCHAR, VARCHAR(n), and TEXT related in PostgreSQL?
They are all the same underlying type. The only difference is the optional length check.
-- All three are identical in PostgreSQL storage
CREATE TABLE test_types (
col_text TEXT,
col_varchar VARCHAR, -- no length = same as TEXT
col_varchar_n VARCHAR(100) -- same type, just adds a length check
);
-- Performance is identical for all three
-- Use TEXT when there is no natural length limit
-- Use VARCHAR(n) when you want a business constraint
What is TOAST and how does it handle large values?
TOAST (The Oversized-Attribute Storage Technique) automatically moves large values out-of-line and compresses them.
Row with a small TEXT value:
┌─────────────────────────────────┐
│ id | customer_id | narrative │ ◄── stored inline in the row
└─────────────────────────────────┘
Row with a large TEXT value:
┌─────────────────────────────────┐
│ id | customer_id | [TOAST ptr] │ ◄── pointer to TOAST table
└─────────────────────────────────┘
│
▼
┌──────────────────────────────────────┐
│ TOAST table: compressed chunks │
│ chunk 1: [compressed narrative data] │
│ chunk 2: [compressed narrative data] │
└──────────────────────────────────────┘
You never manage TOAST manually. It is completely transparent.
How do you migrate CLOB to TEXT?
The standard migration mapping for Oracle to PostgreSQL.
┌───────────────┬────────────────────────────┐ │ Oracle Type │ PostgreSQL Equivalent │ ├───────────────┼────────────────────────────┤ │ CLOB │ TEXT │ │ NCLOB │ TEXT │ │ VARCHAR2(n) │ VARCHAR(n) or TEXT │ │ BLOB │ BYTEA │ │ RAW(n) │ BYTEA │ │ NUMBER(p,s) │ NUMERIC(p,s) │ └───────────────┴────────────────────────────┘
package com.bank.migration;
public class OracleToPostgresMigration {
// In Oracle: DBMS_LOB.GETLENGTH(narrative)
// In PostgreSQL: LENGTH(narrative) -- just a normal function
// In Oracle: DBMS_LOB.SUBSTR(narrative, 1000, 1)
// In PostgreSQL: SUBSTRING(narrative, 1, 1000) -- standard SQL
// LOB locator code collapses into plain queries
}
Can you index a TEXT column?
Yes. You can add B-tree indexes on TEXT for equality and range, and GIN indexes for full-text search.
-- B-tree index on TEXT (for equality or prefix matching)
CREATE INDEX idx_complaint_customer ON complaints (customer_id);
-- GIN full-text search index on TEXT
CREATE INDEX idx_complaint_fts ON complaints
USING GIN (to_tsvector('english', narrative));
-- Full-text search query
SELECT id, customer_id,
ts_headline('english', narrative, to_tsquery('fraud & account'))
FROM complaints
WHERE to_tsvector('english', narrative) @@ to_tsquery('fraud & account');
Oracle CLOB cannot do this without Oracle Text. PostgreSQL TEXT supports full-text search natively.
What about PostgreSQL large objects (lo_* functions)?
PostgreSQL also has a separate large-object facility that mirrors Oracle's streaming locator style.
-- Large object API (rarely needed for text)
SELECT lo_create(0); -- creates a large object OID
-- lo_open, lo_read, lo_write, lo_close
For ordinary large text, always prefer TEXT over large objects. TEXT is simpler, fully transactional, and directly queryable.
How do you use TEXT in Java with JDBC?
Rajesh reads and writes TEXT columns like any other string at National Bank of India.
package com.bank.repository;
import java.sql.*;
public class ComplaintRepository {
public void insertComplaint(Connection conn, long customerId,
String narrative) throws SQLException {
String sql = "INSERT INTO complaints (customer_id, narrative) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, customerId);
ps.setString(2, narrative); // TEXT is just a string
ps.executeUpdate();
}
}
public String getComplaintText(Connection conn, long complaintId)
throws SQLException {
String sql = "SELECT narrative FROM complaints WHERE id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, complaintId);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return rs.getString("narrative");
}
return null;
}
}
}
}
What are common interview follow-up questions?
- Is TEXT slower than VARCHAR? (No, they are the same type in PostgreSQL.)
- When would you use the large-object facility? (Only for truly huge binary files that need streaming.)
- What is the equivalent of Oracle's BLOB? (PostgreSQL
BYTEAfor binary data.)
Visual Overview
Oracle PostgreSQL
┌───────────────────┐ ┌───────────────────┐
│ VARCHAR2(4000) │ ──▶ │ VARCHAR / TEXT │
│ (small strings) │ │ (no size split) │
├───────────────────┤ │ │
│ CLOB │ ──▶ │ TEXT │
│ (up to 4 GB) │ │ (up to ~1 GB) │
│ LOB locator │ │ Plain column │
│ DBMS_LOB API │ │ Standard SQL │
└───────────────────┘ │ TOAST (auto) │
└───────────────────┘
Oracle requires the LOB split.
PostgreSQL unifies everything as TEXT.
Key Takeaway
The PostgreSQL equivalent of Oracle's CLOB is TEXT. In Oracle, CLOB is a special large-character type holding up to about 4 GB, accessed through a LOB locator and DBMS_LOB procedures. PostgreSQL removes that split: TEXT is a native variable-length string with no declared length and a practical ceiling of about 1 GB, and VARCHAR(n), VARCHAR, and TEXT are all the same underlying type.
You read and write a TEXT column with ordinary SELECT and INSERT, with no locator and no streaming API. PostgreSQL transparently moves large values out-of-line into TOAST storage and compresses them. You can even add a GIN full-text index on a TEXT column, which Oracle CLOB cannot do without Oracle Text.
At the National Bank of India, when migrating the complaints system from Oracle to PostgreSQL, the narratives that had lived in CLOB columns became TEXT, and the locator-based access code collapsed into plain queries. Rajesh also added a GIN full-text index for searching complaints.
Remember one sentence: Oracle CLOB becomes PostgreSQL TEXT, a plain unlimited-length string you treat like any normal column.
---