Inserting into Row-Type Columns (IDS)

The following syntax rules apply for inserts on columns that are defined on named ROW types or unnamed ROW types:

• Specify the ROW constructor before the field values to be inserted.
• Enclose the field values of the ROW type in parentheses.
• Cast the ROW expression to the appropriate named ROW type (for named ROW types).

Rows That Contain Named Row Types

The following statement shows you how to insert a row into the employee table in Figure 351:

INSERT INTO employee
   VALUES ('Poole, John', 
   ROW('402 High St', 'Willits', 'CA', 
   ROW(69055,1450))::address_t, 35000 )

Because the address column of the employee table is a named ROW type, you must use a cast operator and the name of the ROW type (address_t) to insert a value of type address_t.

Rows That Contain Unnamed Row Types

Suppose you create the table that Figure 351 shows. The student table defines the s_address column as an unnamed row type.

CREATE TABLE student 
(
s_name     VARCHAR(30),
s_address  ROW(street VARCHAR (20), city VARCHAR(20),
               state CHAR(2), zip VARCHAR(9)),
               grade_point_avg DECIMAL(3,2)
);

The following statement shows you how to add a row to the student table. To insert into the unnamed row-type column s_address, use the ROW constructor but do not cast the row-type value.

INSERT INTO student
   VALUES ('Keene, Terry', 
      ROW('53 Terra Villa', 'Wheeling', 'IL', '45052'),
      3.75)

Specifying 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
      )