TEXT Data Type - PostgreSQL to Oracle Migration

In PostgreSQL, the TEXT data type allows you to store character strings up to 1 GB.

In Oracle, you can use VARCHAR2(4000), VARCHAR2(32767) if MAX_STRING_SIZE initialization parameter is set to EXTENDED or CLOB data types.

PostgreSQL:

  -- Sample table  
  CREATE TABLE items
  (
    name TEXT
  );

MAX_STRING_SIZE = STANDARD by default in Oracle, so the maximum length of VARCHAR2 is 4000 by default.

Oracle:

  -- Sample table  
  CREATE TABLE items
  (
    name VARCHAR2(4000)
  );

The reason is that PostgreSQL TEXT is a native data type that is used by many built-in functions, can be used in an index, in the ORDER BY clause and so on, while Oracle CLOB is very limited data type that can be only used in specific LOB related functions.

In most cases PostgreSQL TEXT is used for short strings, so conversion to VARCHAR2(4000 | 32767) is more practical.

For more information, see PostgreSQL to Oracle Migration.