PostgreSQL Composite Types

Summary: in this tutorial, you will learn how to define PostgreSQL composite types to represent the structure of a row or record.

In PostgreSQL, composite types allow you to define custom data types with multiple fields. These fields can be any built-in or user-defined types, including other composite types.

Defining PostgreSQL composite types

To define a composite type, use the CREATE TYPE statement followed by the type name and a list of fields with their corresponding data types.

Here’s the basic syntax for defining a composite type:

CREATE TYPE type_name AS (
    field1 data_type1,
    field2 data_type2, 
    ...
);Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify the name of the composite type (type_name) after the CREATE TYPE keywords.
Second, define a list of fields of the composite type along with their respective data types.

For example, the following statement defines the address type that stores address information including street, city, state, zip code, and country:

CREATE TYPE address_type AS(
   street text, 
   city text,
   state text, 
   zip_code integer,
   country text
);Code language: SQL (Structured Query Language) (sql)

After defining a composite type, you can use it as a data type of a table column.

For example, the following statement creates a table called contacts whose type of address column is the address_type:

CREATE TABLE contacts(
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address address_type
);Code language: SQL (Structured Query Language) (sql)

Please note that when creating a table, PostgreSQL implicitly creates a corresponding composite type. In this example, PostgreSQL automatically creates the contacts composite type.

Inserting values into a composite column

To construct a composite value, you use the ROW expression syntax:

ROW(value1, value2, ...)Code language: SQL (Structured Query Language) (sql)

In this syntax, the ROW keyword is optional when you have multiple fields in the expression. Therefore, you can simplify the composite value as follows:

(value1, value2, ...)Code language: SQL (Structured Query Language) (sql)

To indicate a NULL, you can use the NULL keyword:

(value1, NULL, value3, ...)Code language: SQL (Structured Query Language) (sql)

Alternatively, you can omit it in the expression:

(value1,, value3, ...)Code language: SQL (Structured Query Language) (sql)

For example, the following statement inserts a new row into the contacts table:

INSERT INTO contacts (name, address)
VALUES (
    'John Smith',
    ROW('123 St', 'Houston', 'TX', 77001, 'USA')
);Code language: SQL (Structured Query Language) (sql)

In this example, we use the following composite value to insert into the address column:

ROW('123 St', 'Houston', 'TX', 77001, 'USA')Code language: SQL (Structured Query Language) (sql)

The following statement inserts a value into individual fields of the address column:

INSERT INTO contacts (
    name,
    address.street,
    address.city,
    address.state,
    address.zip_code
  )
VALUES
  ('Jane Doe', '4000 N. 1st Street', 'San Jose', 'CA', 95134);Code language: SQL (Structured Query Language) (sql)

In this statement, we use the column name, followed by a dot, and field name to indicate the field of a composite type.

Querying composite values

The following statement retrieves the rows from the contacts table:

SELECT * FROM contacts;Code language: SQL (Structured Query Language) (sql)

Output:

 id |    name    |                   address
----+------------+---------------------------------------------
  1 | John Smith | ("123 St",Houston,TX,77001,USA)
  2 | Jane Doe   | ("4000 N. 1st Street","San Jose",CA,95134,)
(2 rows)Code language: SQL (Structured Query Language) (sql)

To query individual fields of a composite type, you use the following syntax:

(column_name).field_nameCode language: SQL (Structured Query Language) (sql)

For example, the following statement retrieves the id, name, city, state, and zip code of contacts:

SELECT
  id,
  name,
  (address).city,
  (address).state,
  (address).zip_code
FROM
  contacts;Code language: SQL (Structured Query Language) (sql)

Output:

 id |    name    |   city   | state | zip_code
----+------------+----------+-------+----------
  1 | John Smith | Houston  | TX    |    77001
  2 | Jane Doe   | San Jose | CA    |    95134
(2 rows)Code language: SQL (Structured Query Language) (sql)

If you retrieve all fields from a composite value, you can use the asterisk (*) shorthand:

SELECT
  id,
  name,
  (address).*
FROM
  contacts;Code language: SQL (Structured Query Language) (sql)

Output:

 id |    name    |       street       |   city   | state | zip_code | country
----+------------+--------------------+----------+-------+----------+---------
  1 | John Smith | 123 St             | Houston  | TX    |    77001 | USA
  2 | Jane Doe   | 4000 N. 1st Street | San Jose | CA    |    95134 | null
(2 rows)Code language: SQL (Structured Query Language) (sql)

Updating composite values

The following example updates the country of the contact id 2 to USA:

UPDATE contacts
SET
  address.country= 'USA'
WHERE
  id = 2
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

 id |   name   |                    address
----+----------+------------------------------------------------
  2 | Jane Doe | ("4000 N. 1st Street","San Jose",CA,95134,USA)
(1 row)Code language: SQL (Structured Query Language) (sql)

In this example, you cannot put the parentheses around the column name of the composite type after the SET keyword.

Summary

Composite types allow you to define custom data types that include multiple fields.

Was this tutorial helpful ?