User-defined type (UDT) column
If a table specifies a user-defined type (UDT) to hold data, then use INSERT
or UPDATE
to enter data.
Prerequisites
-
Set or replace user-defined type data, using the
INSERT
orUPDATE
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 the inclusion of |
-
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'
}
]
);
The UDT nested in the list is frozen, so the entire list will be read when querying the table. |
Updating individual field data in a UDT
-
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 theCREATE 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
--------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------+----------
e7ae5cf3-d358-4d99-b900-85902fda9bb0 | {birthday: '1993-06-18 00:00:00.000000+0000', nationality: 'New Zealand', height: '72', weight: '175', next_race: 'Tour de France'} | FRAME
(1 rows)
The resulting birthday
change is shown.
The nationality
, height
, and weight
values are unchanged.
UDTs with collection fields must be frozen in table creation, and individual field values cannot be updated. |