MODIFY Column Compatibility

1. Purpose

In Oracle compatibility mode, IvorySQL supports ALTER TABLE … MODIFY to change the type and nullability of existing columns, including the common migration syntax MODIFY (column_name data_type NOT NULL).

2. Function Description

2.1. Syntax

ALTER TABLE table_name MODIFY column_definition;
ALTER TABLE table_name MODIFY (column_definition [, ...]);

Column definitions for type and nullability changes take the following forms:

column_name data_type
column_name { NOT NULL | NULL }
column_name data_type { NOT NULL | NULL }

Parentheses are optional for a single column. To modify several columns in one MODIFY clause, enclose their definitions in parentheses and separate them with commas.

Modification Behavior

Modification	Behavior
Type only	Changes the column type or length and preserves its nullability
`NOT NULL`	Requires non-null values and validates existing data
`NULL`	Removes the column’s non-null requirement without changing existing data
Type with `NOT NULL` or `NULL`	Changes the type and nullability in one ALTER TABLE statement

Type only

Changes the column type or length and preserves its nullability

NOT NULL

Requires non-null values and validates existing data

NULL

Removes the column’s non-null requirement without changing existing data

Type with NOT NULL or NULL

Changes the type and nullability in one ALTER TABLE statement

Type changes follow the underlying ALTER TABLE conversion and dependency checks. Increasing a character column’s length is a common use case. Other conversions depend on the source and target types, existing data, and associated constraints; accepting the syntax does not allow arbitrary type conversions.

2.2. Retained Column Attribute Syntax

MODIFY also retains the existing syntax for column visibility and identity attributes:

ALTER TABLE table_name MODIFY (column_name INVISIBLE);
ALTER TABLE table_name MODIFY column_name VISIBLE;
ALTER TABLE table_name MODIFY (column_name GENERATED ALWAYS AS IDENTITY);
ALTER TABLE table_name MODIFY (column_name GENERATED BY DEFAULT AS IDENTITY);
ALTER TABLE table_name MODIFY (column_name DROP IDENTITY);

Changing the identity generation mode applies to an existing identity column and remains subject to the corresponding attribute checks. For visibility behavior, see Invisible Columns.

3. Examples

Run the following examples in order in an Oracle compatibility mode psql session. Use autocommit for examples containing an expected error so that the error does not leave an explicit transaction aborted.

3.1. Change a Type and Set NOT NULL

CREATE TABLE modify_demo (a VARCHAR2(10), b VARCHAR2(10), c NUMBER);
INSERT INTO modify_demo VALUES (NULL, 'x', 1);

ALTER TABLE modify_demo MODIFY (a VARCHAR2(20) NOT NULL);

Column a contains NULL, so the modification fails with the following main error message:

ERROR:  column "a" of relation "modify_demo" contains null values

Resolve the existing NULL and retry. The modification to column b demonstrates the form without parentheses.

UPDATE modify_demo SET a = 'y';
ALTER TABLE modify_demo MODIFY (a VARCHAR2(20) NOT NULL);
ALTER TABLE modify_demo MODIFY b VARCHAR2(30) NOT NULL;

Both a and b now require non-null values. A failed ALTER TABLE does not retain just the type change.

3.2. Change Nullability Separately and Modify Multiple Columns

ALTER TABLE modify_demo MODIFY (a NULL);
ALTER TABLE modify_demo MODIFY b NULL;
ALTER TABLE modify_demo MODIFY (a VARCHAR2(40) NOT NULL, c NUMBER NOT NULL);

INSERT INTO modify_demo VALUES ('012345678901234567890123456789', 'longer value', 2);
SELECT c, length(a) AS a_length FROM modify_demo ORDER BY c;

The result shows that existing data is preserved and the widened column accepts longer values:

 c | a_length
---+----------
 1 |        1
 2 |       30
(2 rows)

3.3. Preserve Nullability When Changing Only the Type

ALTER TABLE modify_demo MODIFY (c NUMBER(10));
ALTER TABLE modify_demo MODIFY b VARCHAR2(40);
\d modify_demo

Column a is now varchar2(40) NOT NULL, b is nullable varchar2(40), and c is number(10,0) NOT NULL. A type change without NULL or NOT NULL does not reset the existing nullability.

DROP TABLE modify_demo;

4. Usage Notes

The caller needs permission to alter the target table. Permission, locking, and dependency checks follow the existing ALTER TABLE implementation.
Resolve existing NULL values before setting NOT NULL. Removing a non-null requirement remains subject to related constraints, such as primary keys.
One MODIFY can expand into several internal operations, but the statement still follows transaction rules. If any operation fails, the statement’s changes are rolled back.
Type changes or non-null validation can scan or rewrite the table. Execution time and locking impact depend on table size, types, and constraints.