When we have a column with composite data in PostgreSQL, we can access each field by using dot notation. This is true whether we select the data or update it. This means that we can use dot notation to update individual fields within a composite column.

And if we need to update all fields at once, we can use the ROW constructor.

Sample Data

Suppose we have a table with the following data:

SELECT * FROM person;

Result:

 person_id | first_name | last_name |                    address_details                     
-----------+------------+-----------+--------------------------------------------------------
         1 | Flint      | Barker    | ("7 Stone St",Cairns,QLD,4784,AUS)
         2 | Jack       | Rubble    | ("102 Peel Road",Milton,Attapeu,16120,LAO)
         3 | Abbie      | Pabrai    | ("34 Tank Pakhadi Road",Mumbai,Maharashtra,400001,IND)
         4 | Monish     | Kumar     | ("12 Shish St",Mumbai,Maharashtra,400001,IND)
(4 rows)

We can see that the address_details column contains composite data. Composite data contains multiple values, separated by a comma, and the whole list is enclosed in parentheses.

We can return each field of the composite column like this:

SELECT (address_details).* FROM person;

Result:

        street        |  city  |    state    | postcode | country_code 
----------------------+--------+-------------+----------+--------------
 7 Stone St           | Cairns | QLD         | 4784     | AUS
 102 Peel Road        | Milton | Attapeu     | 16120    | LAO
 34 Tank Pakhadi Road | Mumbai | Maharashtra | 400001   | IND
 12 Shish St          | Mumbai | Maharashtra | 400001   | IND
(4 rows)

When we do this, we can see the names of the individual fields that make up the composite value. We can use these names when updating an individual field.

Update an Individual Composite Value

Here’s an example of updating an individual field:

UPDATE person 
SET address_details.street = '31 Lake Ave' 
WHERE person_id = 1;

SELECT * FROM person WHERE person_id = 1;

Result:

 person_id | first_name | last_name |           address_details           
-----------+------------+-----------+-------------------------------------
         1 | Flint      | Barker    | ("31 Lake Ave",Cairns,QLD,4784,AUS)
(1 row)

In this case I used dot notation to refer to the specific field that I wanted to update within the composite value. All other fields were untouched.

Update All Composite Fields

We can use a ROW constructor to update all composite fields of a given row at once:

UPDATE person 
SET address_details = ROW('47 Lucky Lane', 'Dubbo', 'NSW', '2830', 'AUS' )
WHERE person_id = 3;

SELECT * FROM person WHERE person_id = 3;

Result:

 person_id | first_name | last_name |           address_details            
-----------+------------+-----------+--------------------------------------
         3 | Abbie      | Pabrai    | ("47 Lucky Lane",Dubbo,NSW,2830,AUS)
(1 row)

The ROW keyword is optional when we’re updating multiple fields at once. So we could achieve the same result like this:

UPDATE person 
SET address_details = ('47 Lucky Lane', 'Dubbo', 'NSW', '2830', 'AUS' )
WHERE person_id = 3;

Another way of updating all fields of a composite value is by writing the composite value as a literal constant:

UPDATE person 
SET address_details = '("1 Wave St","Jervis Bay","NSW","2540","AUS")'
WHERE person_id = 3;

SELECT * FROM person WHERE person_id = 3;

Result:

 person_id | first_name | last_name |             address_details             
-----------+------------+-----------+-----------------------------------------
         3 | Abbie      | Pabrai    | ("1 Wave St","Jervis Bay",NSW,2540,AUS)
(1 row)

Note that any space between the comma and the value will be included in the value, and so if you don’t want the extra space in the stored value, then make sure there’s no space there when updating/inserting the data.