Understanding Composite Types in PostgreSQL

Posted on by Ian

In PostgreSQL, a composite type is a kind of data type that consists of multiple data types. It consists of a list of field names and their respective data types.

We can use composite types to fit multiple fields into one column.

We can create our own custom composite types, and they can be used in many of the same scenarios that simple data types can be used.

Creating a Composite Type

We can create our own composite types by using the CREATE TYPE statement.

Here’s an example:

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

That code creates a composite type called address. It consists of five fields and their respective data types.

Another way to create a composite type is to simply create a table. When we create a table, PostgreSQL automatically creates a composite type behind the scenes with the same name as the table. Each column name becomes a field name in the composite type. No constraints are copied to the composite type though – composite types don’t support constraints (at least not as of PostgreSQL 16). It is possible to create a domain over the composite type, and then create any constraints as CHECK constraints of the domain.

It’s also quite possible to use these automatically created composite types if required.

Using a Composite Type

Once created, a composite type can be used in tables or functions, just like any other data type.

For example:

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

This table has a column called address_details that uses our address composite type, while all other columns use the normal built-in types.

As alluded to previously, creating this table will also create a composite type called person, with fields that reflect the columns and their types.

We could go ahead and create another table using that automatically created composite type (we’ll do that later).

Inserting Composite Data

We have a few options when it comes to inserting data into a column with a composite type.

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 the row constructor started with the ROW keyword, but when we have multiple values, we can omit this keyword:

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

We can also insert the composite value as a literal constant. To do this, we need to enclose the parentheses in single quotes. This means that any string values need to be enclosed in double quotes (as opposed to single quotes like in the previous examples):

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

Notice that I also removed the spaces between the fields? Here’s what happens if I retain the spaces:

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

Result:

ERROR:  value too long for type character(3)
LINE 5:     '("34 Tank Pakhadi Road", "Mumbai", "Maharashtra", "4000...
            ^

This error occurred because any white space between the parentheses is considered part of the field value. In this case the last field (country_code) is char(3), which means it can store no more than three characters. However, the whitespace adds another character to the three already specified, and so it’s trying to insert four characters into a three character field.

That’s why we didn’t get the error in the previous example, where there was no white space in the field.

Any white space outside of the parentheses is ignored.

Yet another way to insert composite data is by 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.

Querying Composite Data

Let’s select all data from the table:

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 composite data is enclosed in parentheses, just as we inserted it. This is how the column will be returned if we simply select the column by its name:

SELECT address_details FROM person;

Result:

                    address_details                     
--------------------------------------------------------
 ("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)

Of course, this isn’t the only way we can access data in the column. We can also access the individual fields in the column by using dot notation to name the field in question:

SELECT (address_details).country_code FROM person;

Result:

 country_code 
--------------
 AUS
 LAO
 IND
 IND
(4 rows)

Notice that I enclosed the column name in parentheses. I did this so as not to confuse the parser. Had I omitted the parentheses, it might have confused address_details with a table name and country_code for the column name.

Anyway, we can also use the asterisk wildcard (*) to return all fields from the composite column:

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)

So we can see that the composite values are returned as if they are stored in their own columns.

Again, I surrounded the column name with parentheses so that the parser didn’t mistake it for a table name.

And we can use the same concept when referring to composite data when filtering the results, such as when using the WHERE clause:

SELECT * FROM person
WHERE (address_details).city = 'Mumbai';

Result:

 person_id | first_name | last_name |                    address_details                     
-----------+------------+-----------+--------------------------------------------------------
         3 | Abbie      | Pabrai    | ("34 Tank Pakhadi Road",Mumbai,Maharashtra,400001,IND)
         4 | Monish     | Kumar     | ("12 Shish St",Mumbai,Maharashtra,400001,IND)
(2 rows)

Updating Composite Data

We have a couple of options when it comes to updating composite values.

We can update individual fields:

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)

Or we can go ahead and update all composite fields 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)

Nested Composite Types

It’s possible for a composite type to include a composite type in its own definition.

As mentioned previously, a composite type is automatically created whenever we create a table. The composite type gets the same name as the table.

So when we created the person table previously, a composite type called person was automatically created behind the scenes.

We could create a person2 table that uses that composite type:

CREATE TABLE person2 (
    details person
    );

This table has one column called details. That column uses the person composite type that was automatically created when we created the person table.

We could insert the same data and query it just like we could with the other table.

For example:

INSERT INTO person2 (details)
VALUES (
    ROW( 1, 'Flint', 'Barker', ('7 Stone St', 'Cairns', 'QLD', '4784', 'AUS'))
);

SELECT * FROM person2;

Result:

                         details                         
---------------------------------------------------------
 (1,Flint,Barker,"(""7 Stone St"",Cairns,QLD,4784,AUS)")
(1 row)

The difference is that in this case we need to insert all data as composite data (because the only column is a composite type).

When we retrieve the data from the column, we can see that it’s all composite data.

We can use dot notation to retrieve the data, just like we did in the previous examples (although, we’ll need to go an extra level down when querying data from the person2 table).

For example:

SELECT 
    (details).person_id,
    (details).first_name,
    ((details).address_details).street
FROM person2;

Result:

 person_id | first_name |   street   
-----------+------------+------------
         1 | Flint      | 7 Stone St
(1 row)