User-defined type (UDT) column

Write values into a user-defined type (UDT) column.

• Set or replace user-defined type data, using the INSERT or UPDATE statement, and enclosing the user-defined type with curly brackets, separating each key-value pair in the user-defined type by a colon.

  INSERT INTO cycling.cyclist_stats (
    id,
    lastname,
    basics
  ) VALUES (
    e7ae5cf3-d358-4d99-b900-85902fda9bb0,
    'FRAME',
    { birthday:'1993-06-18', nationality:'New Zealand', weight:null, height:null }
  );

  Note: Note the inclusion of null values for UDT elements that have no value. A value, whether null or otherwise, must be included for each element of the UDT.
• Data can be inserted into a UDT that is nested in another column type. For example, a list of races, where the race name, date, and time are defined in a UDT has elements enclosed in curly brackets that are in turn enclosed in square brackets.

  INSERT INTO cycling.cyclist_races (
    id,
    lastname,
    firstname,
    races
  ) VALUES (
    5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
    'VOS',
    'Marianne',
    [ {
         race_title:'Rabobank 7-Dorpenomloop Aalburg',
         race_date:'2015-05-09',
         race_time:'02:58:33'},
       {
         race_title:'Ronde van Gelderland',
         race_date:'2015-04-19',
         race_time:'03:22:23'
       }
    ]
  );

  Note: The UDT nested in the list is frozen, so the entire list will be read when querying the table.

• User-defined types that include only non-collection fields can update individual field values. Update an individual field in user-defined type data using the UPDATE statement. The desired key-value pair are defined in the command. In order to update, the UDT must be defined in the CREATE TABLE statement as an unfrozen data type.

  CREATE TABLE IF NOT EXISTS cycling.cyclist_stats ( 
    id UUID PRIMARY KEY,
    lastname text,
    basics basic_info
  );

  INSERT INTO cycling.cyclist_stats (
    id, lastname,
    basics
  ) VALUES (
    220844bf-4860-49d6-9a4b-6b5d3a79cbfb,
    'TIRALONGO',
    { birthday:'1977-07-08', nationality:'Italy', weight:'63 kg', height:'1.78 m' }
  );

  UPDATE cycling.cyclist_stats 
  SET basics.birthday = '2000-12-12' 
  WHERE id = 220844bf-4860-49d6-9a4b-6b5d3a79cbfb;

  The UDT is defined in the table with basics basic_info. This example shows an inserted row, followed by an update that only updates the value of birthday inside the UDT basics.

  SELECT *
  FROM cycling.cyclist_stats
  WHERE id = 220844bf-4860-49d6-9a4b-6b5d3a79cbfb;

   id                                   | basics                                                                                             
      | lastname
  --------------------------------------+----------------------------------------------------------------------------------------------------
  ----+-----------
   220844bf-4860-49d6-9a4b-6b5d3a79cbfb | {birthday: '2000-12-12 00:00:00.000000+0000', nationality: 'Italy', height: '1.78 m', weight: '63 k
  g'} | TIRALONGO
  
  (1 rows)

  The resulting birthday change is shown. The nationality, height, and weightvalues are unchanged.

  Note: UDTs with collection fields must be frozen in table creation, and individual field values cannot be updated.