Specify NULL values for row types

The fields of a row-type column can contain NULL values. You can specify NULL values either at the level of the column or the field.

The following statement specifies a NULL value at the column level to insert NULL values for all fields of the s_address column. When you insert a NULL value at the column level, do not include the ROW constructor.

INSERT INTO student VALUES ('Brauer, Howie', NULL, 3.75);

When you insert a NULL value for particular fields of a ROW type, you must include the ROW constructor. The following INSERT statement shows how you might insert NULL values into particular fields of the address column of the employee table. (The address column is defined as a named ROW type.)

INSERT INTO employee
   VALUES (
      'Singer, John',
      ROW(NULL, 'Davis', 'CA', 
      ROW(97000, 2000))::address_t, 67000
      );

When you specify a NULL value for the field of a ROW type, you do not need to explicitly cast the NULL value when the ROW type occurs in an INSERT statement, an UPDATE statement, or a program variable assignment.

The following INSERT statement shows how you insert NULL values for the street and zip fields of the s_address column for the student table:

INSERT INTO student
   VALUES( 
      'Henry, John',
      ROW(NULL, 'Seattle', 'WA', NULL), 3.82
      );