ALTER TABLE SET NOT NULL - PostgreSQL to Oracle Migration

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

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

PostgreSQL:

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

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

Oracle:

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

For more information, see PostgreSQL to Oracle Migration.