VARCHAR Data Type - PostgreSQL to Oracle Migration

In PostgreSQL, the VARCHAR(n) data type stores variable-length character strings up to n characters. If n is not defined VARCHAR can store up to 1 GB (bytes).

In Oracle, you can use VARCHAR2(n) with the maximum size of 4000 or 32767 bytes if MAX_STRING_SIZE initialization parameter is set to EXTENDED (not set by default) or CLOB data types.

PostgreSQL:

  -- Sample table  
  CREATE TABLE specs
  (
    note VARCHAR(30),
    data VARCHAR          -- up to 1 GB
  );

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

Oracle:

  -- Sample table  
  CREATE TABLE specs
  (
    note VARCHAR2(30 CHAR),
    data VARCHAR2(4000 CHAR)
  );

Conversion summary:

Although PostgreSQL can store up to 1 GB in VARCHAR column, the maximum value of n parameter is 10,485,760:

PostgreSQL:

  CREATE TABLE t1 
  (
    c1 VARCHAR(20000000)   -- 20 millions
  );
  # ERROR:  length for type varchar cannot exceed 10485760

So you have to skip n parameter for large strings:

PostgreSQL:

  CREATE TABLE t1 
  (
    c1 VARCHAR    -- Up to 1 GB
  );
  # Ok

For more information, see PostgreSQL to Oracle Migration.