ALTER TABLE DROP NOT NULL - PostgreSQL to Oracle Migration

You can use ALTER TABLE name ALTER COLUMN statement to set NULL property (drop NOT NULL) for one or more columns in PostgreSQL.

You can also use ALTER TABLE to set NULL (nullable) for columns in Oracle, but the syntax is different, so the conversion is required.

PostgreSQL:

  -- Sample table  
  CREATE TABLE items
  (
    name VARCHAR(30) NOT NULL,
    note VARCHAR(10)  NOT NULL,
    cnt  INT  NOT NULL
  );
 
  -- Set NULL
  ALTER TABLE items ALTER COLUMN name DROP NOT NULL;
 
  -- Set NULL for multiple columns
  ALTER TABLE items ALTER note DROP NOT NULL, ALTER cnt DROP NOT NULL;

In Oracle, the COLUMN keyword is not used (and it is optional in PostgreSQL), and you have to use parentheses () to set NULL for multiple columns:

Oracle:

  -- Set NULL
  ALTER TABLE items MODIFY name NULL;
 
  -- Set NOT NULL for multiple columns
  ALTER TABLE items MODIFY (note NULL, cnt NULL);

For more information, see PostgreSQL to Oracle Migration.