In PostgreSQL, a composite value is stored as a comma separated list of values, surrounded by parentheses.

When we have a table that contains composite values, we can extract individual fields by using dot notation to refer to the specific field that we want to extract.

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 values. Composite values contain multiple values, separated by a comma, and the whole list is enclosed in parentheses.

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)

But this isn’t the only way we can access composite data.

Extract All Fields From the Composite Value

We can use dot notation to extract individual fields from the composite value. We can refer to a specific field name or we can use the asterisk wildcard (*) to return all fields from the composite column.

Let’s first select all fields:

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)

The fields’ values are returned as if they are stored in their own columns. They even have their own field names. These field names are the names that were given when the composite type was created.

Here’s the code that was used to create the composite type for the above data:

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

We can see that the field names match the column names of the results that were returned by our query.

Here’s the code that was used to create the table:

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

We can see that the address_details column uses the address composite type defined in the CREATE TYPE code above.

Extract a Specific Value

Here’s an example of using dot notation to return a specific field from the composite value:

SELECT (address_details).country_code FROM person;

Result:

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

Filtering by Composite Data

We can use the same dot notation technique when using composite data to filter 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)

Omitting the Parentheses

In the previous examples 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.

Here’s what happens when I remove the parentheses:

SELECT address_details.country_code FROM person;

Result:

ERROR:  missing FROM-clause entry for table "address_details"
LINE 1: SELECT address_details.country_code FROM person;
               ^

If we want to include the table name, we should include that inside the same parentheses as the column name:

SELECT (person.address_details).country_code FROM person;

Result:

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