PostgreSQL Composite Types

Summary: In this tutorial, you’ll learn how to use the PostgreSQL composite types and effectively manipulate composite columns.

Introduction to PostgreSQL composite types #

In PostgreSQL, a composite type represents the structure of a row or record. A composite type is a list of field names and their data types.

PostgreSQL allows you to use composite types in the same way as simple types.

For example, you can use composite types for table columns, function arguments, and function return types.

Creating composite types #

To create a new composite type, you use the CREATE TYPE statement as follows:

CREATE TYPE type_name AS (
    field1 data_type,
    field2 data_type,
    ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, provide the composite type name after the CREATE TYPE keywords.
Second, list field names with their corresponding data types within parentheses after the AS keyword.

For example, the following statement creates a composite type called coordinate that includes latitude and longitude:

CREATE TYPE coordinate AS (
    latitude DEC,
    longitude DEC
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Using composite types #

After creating a composite type, you can use it like a simple type. For example, you can use a composite type as the type of a table column.

The following statement creates a table called warehouse_locations that uses the coordinate composite type:

CREATE TABLE warehouse_locations(
    warehouse_id INT PRIMARY KEY,
    location coordinate
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Constructing composite values #

To create a composite value as a literal constant, you can use the ROW keyword and enclose comma-separated field values within parentheses as follows:

ROW(value1, value2, ...)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, you can construct the value for the coordinate composite type like this:

ROW(37.318686, -121.871019)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement inserts a new row into the warehouse_locations table:

INSERT INTO warehouse_locations(warehouse_id, location)
VALUES(1, ROW(37.318686, -121.871019))
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 warehouse_id |        location
--------------+-------------------------
            1 | (37.318686,-121.871019)

It’s possible to explicitly specify the fields of the composite column you want to insert using the dot notation:

composite_column.field_nameCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

INSERT INTO
  warehouse_locations (
    warehouse_id,
    location.latitude,
    location.longitude
  )
VALUES
  (2, 37.650972, -122.398659)
RETURNING
  *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 warehouse_id |        location
--------------+-------------------------
            2 | (37.650972,-122.398659)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Accessing fields of composite types #

You can access the fields of a composite column using the dot notation:

composite_column.field_nameCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Sometimes, you need to place the composite column within parentheses like this:

(composite_column).field_nameCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To access all fields of a composite column, you can use the asterisk shorthand (*):

(composite_column).*Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement retrieves the latitude and longitude together with warehouse_id from the warehouse_locations table:

SELECT
  warehouse_id,
  (location).latitude,
  (location).longitude
FROM
  warehouse_locations;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 warehouse_id | latitude  |  longitude
--------------+-----------+-------------
            1 | 37.318686 | -121.871019
            2 | 37.650972 | -122.398659

In the SELECT statement, if you don’t use the parentheses, PostgreSQL might misinterpret the location type as a table name and issue an error.

The above query is equivalent to the following query that uses the shorthand (*):

SELECT
    warehouse_id,
    (location).*
FROM
    warehouse_locations;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 warehouse_id | latitude  |  longitude
--------------+-----------+-------------
            1 | 37.318686 | -121.871019
            2 | 37.650972 | -122.398659

Updating composite values #

The following statement updates the latitude and longitude of the location of the warehouse with id 1:

UPDATE warehouse_locations
SET
  location.latitude = 37.650971,
  location.longitude = -122.398658
WHERE
  warehouse_id = 1
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 warehouse_id |        location
--------------+-------------------------
            1 | (37.650971,-122.398658)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You do not use parentheses when you access the field in the SET clause of the UPDATE statement because the dot notation is sufficient to identify the individual fields within the composite type. Using parentheses here will result in an error.

To update all fields of the location column at once, you can also use the following syntax:

UPDATE warehouse_locations
SET
  location = ROW (37.650971, -122.398658)
WHERE
  warehouse_id = 1
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 warehouse_id |        location
--------------+-------------------------
            1 | (37.650971,-122.398658)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Deleting rows based on composite values #

When using the DELETE statement to delete a row based on a field of a composite type, you do need to use the parentheses in the WHERE clause. The reason is that the WHERE clause qualifies the column with the table name. If you don’t use the parentheses, PostgreSQL may misinterpret the composite as a table.

For example, the following statement deletes a row from the warehouse_locations table where the latitude of a location equals 37.650971:

DELETE FROM warehouse_locations
WHERE
  (location).latitude = 37.650971
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 warehouse_id |        location
--------------+-------------------------
            1 | (37.650971,-122.398658)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Applying constraints #

Composite types are flexible. However, they do not directly support constraints such as NOT NULL and CHECK on individual fields.

For example, if you apply the NOT NULL constraint to the warehouse_locations table:

ALTER TABLE warehouse_locations
ALTER COLUMN location
SET NOT NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

The NOT NULL constraint ensures that the whole composite value is NOT NULL, not individual fields.

Therefore, the following statement fails to insert NULL into the location column:

INSERT INTO warehouse_locations(warehouse_id, location)
VALUES(3, NULL);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Error:

null value in column "location" of relation "warehouse_locations" violates not-null constraintCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

But you can insert NULL into latitude and longitude individually within a ROW:

INSERT INTO
  warehouse_locations (warehouse_id, location)
VALUES
  (3, ROW (NULL, NULL));Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

To apply constraints like NOT NULL or CHECK to individual fields of a composite column, you can create a domain over the composite type and apply constraints to the domain. For example:

First, remove the row with id 3 from the warehouse_locations table (if it exists):

DELETE FROM warehouse_locations 
WHERE warehouse_id = 3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a domain coordinate_domain over the coordinate type:

CREATE DOMAIN coordinate_domain 
AS coordinate 
CHECK (
  (VALUE).latitude IS NOT NULL
  AND (VALUE).longitude IS NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, change the type of the location column to coordinate_domain:

ALTER TABLE warehouse_locations
ALTER COLUMN location TYPE coordinate_domain;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Finally, attempt to insert NULL into individual fields of the location:

INSERT INTO
  warehouse_locations (warehouse_id, location)
VALUES
  (3, ROW (NULL, NULL));Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

It will result in an error as expected:

ERROR:  value for domain coordinate_domain violates check constraint "coordinate_domain_check"Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Implicit composite types #

When you create a table, PostgreSQL automatically creates a corresponding composite type.

CREATE TABLE product_serials (
  serial_no VARCHAR(25) PRIMARY KEY,
  product_id INT NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

PostgreSQL automatically creates a product_serials composite type called product_serials with two fields serial_no and product_id. However, it does not carry the constraints to the type.

Summary #

A composite type represents a record or a row.
A composite type contains a list of fields and their corresponding data types.
Use a composite type similarly to a simple type.
Composite types do not directly support constraints on individual fields, but you can create a domain over the composite type and apply constraints to the domain.
PostgreSQL automatically creates a corresponding implicit composite type for each table.

Quiz #

Was this tutorial helpful ?