When we have a column that’s defined as a composite type in PostgreSQL, we have some options when it comes to inserting data. For example, we can explicitly specify each individual field of the composite type, or we can use a row constructor to insert all fields at once.

Below are four different options for inserting composite values into a column in PostgreSQL.

Sample Composite Type and Table

Suppose we create the following composite type and a table that uses that type:

CREATE TYPE address AS(
   street text, 
   city varchar(200),
   state varchar(50),
   postcode varchar(12),
   country_code char(3)
);

CREATE TABLE person (
    person_id serial primary key,
    first_name varchar(100),
    last_name varchar(100),
    address_details address
    );

We can use the following options to insert data into that table.

Option 1: Row Constructor using the ROW Keyword

We can use a row constructor to specify the values for each field:

INSERT INTO person (first_name, last_name, address)
VALUES (
    'Flint',
    'Barker',
    ROW('7 Stone St', 'Cairns', 'QLD', '4784', 'AUS')
);

In this example, we use the ROW expression syntax, where we prefix the parenthesised list of values with the ROW keyword.

Option 2: Row Constructor Without the ROW Keyword

When we insert multiple values with a row constructor, we have the option of leaving the ROW keyword out. Therefore we can do the following:

INSERT INTO person (first_name, last_name, address)
VALUES (
    'Jack',
    'Rubble',
    ('102 Peel Road', 'Milton', 'Attapeu', '16120', 'LAO')
);

So this is almost the same as the previous example, except that we omitted the ROW keyword (and we inserted different values of course).

Option 3: Insert a Literal Constant

Another way to insert a composite value is to use a literal constant:

INSERT INTO person (first_name, last_name, address_details)
VALUES (
    'Abbie',
    'Pabrai',
    '("34 Tank Pakhadi Road","Mumbai","Maharashtra","400001","IND")'
);

When we do this, we enclose the whole thing in single quotes. The double quotes are sort of optional, depending on the data. We can put double quotes around any field value, but we must do so if the field value contains commas or parentheses.

Note that when we do this, any white space inside the parentheses is included in the value. That’s why I ensured that there was no space between the commas and the field values.

Option 4: Specify Each Field Separately

We can also insert composite values by explicitly specifying each field separately. We can do this with dot notation:

INSERT INTO person (
    first_name, 
    last_name, 
    address_details.street,
    address_details.city,
    address_details.state,
    address_details.postcode,
    address_details.country_code
    )
VALUES (
    'Monish',
    'Kumar',
    '12 Shish St',
    'Mumbai',
    'Maharashtra',
    '400001',
    'IND'
);

In this case, the fields are prefixed with the column name. The values appear in the VALUES clause just like any other value.