Updating Row Types (IDS)

The syntax you use to update a row-type value differs somewhat depending on whether the column is a named ROW type or unnamed ROW type. This section describes those differences and also describes how to specify NULL values for the fields of a ROW type.

Updating Rows That Contain Named Row Types

To update a column that is defined on a named ROW type, you must specify all fields of the ROW type. For example, the following statement updates only the street and city fields of the address column in the employee table, but each field of the ROW type must contain a value (NULL values are allowed):

UPDATE employee
  SET address = ROW('103 California St', 
    San Francisco', address.state, address.zip)::address_t
  WHERE name = 'zawinul, joe'

In this example, the values of the state and zip fields are read from and then immediately reinserted into the row. Only the street and city fields of the address column are updated.

When you update the fields of a column that are defined on a named ROW type, you must use a ROW constructor and cast the row value to the appropriate named ROW type.

Updating Rows That Contain Unnamed Row Types

To update a column that is defined on an unnamed ROW type, you must specify all fields of the ROW type. For example, the following statement updates only the street and city fields of the address column in the student table, but each field of the ROW type must contain a value (NULL values are allowed):

UPDATE student
   SET s_address = ROW('13 Sunset', 'Fresno', 
   s_address.state, s_address.zip)
   WHERE s_name = 'henry, john'

To update the fields of a column that are defined on an unnamed ROW type, always specify the ROW constructor before the field values to be inserted.

Specifying Null Values for the Fields of a Row Type

The fields of a row-type column can contain null values. When you insert into or update a row-type field with a NULL value, you must cast the value to the data type of that field.

The following UPDATE statement shows how you might specify NULL values for particular fields of a named row-type column:

UPDATE employee
   SET address = ROW(NULL::VARCHAR(20), 'Davis', 'CA',
   ROW(NULL::CHAR(5), NULL::CHAR(4)))::address_t)
   WHERE name = 'henry, john'

The following UPDATE statement shows how you specify NULL values for the street and zip fields of the address column for the student table.

UPDATE student
   SET address = ROW(NULL::VARCHAR(20), address.city,
   address.state, NULL::VARCHAR(9))
   WHERE s_name = 'henry, john'