Home » PostgreSQL Tutorial » PostgreSQL Composite Types

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.

In this tutorial, you will learn how to:

  • Create a new composite type.
  • Use a composite type for a table column.
  • Construct, access, and update composite values.
  • Inspect the fields of a composite type.
  • Understand how NULL works with composite values.
  • Apply constraints to fields using a domain over a composite type.

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,
    ...
);

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.

Note that a composite type defines the structure of a value. It does not create a table or store any rows.

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

CREATE TYPE coordinate AS (
    latitude DEC,
    longitude DEC
);

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
);

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, ...)

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

ROW(37.318686, -121.871019)

In this value, 37.318686 is the latitude, and -121.871019 is the longitude.

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 *;

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_name

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
  *;

Try it

Output:

 warehouse_id |        location
--------------+-------------------------
            2 | (37.650972,-122.398659)

Accessing fields of composite types #

When you want to read a field from a composite-valued column or expression, you use the following syntax:

(composite_column).field_name

For exampl,e you can access the fields of the location field as follows:

(location).latitude
(location).longitude

The parentheses group location as the composite expression before PostgreSQL applies the field selector.

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

(composite_column).*

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;

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;

Try it

Output:

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

Updating composite values #

You can use an UPDATE statement to modify either individual iedl ỏ the complete composite value. To assign a value to an invididual field, you can place the field selector on the left side of the assignement:

For example, 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 *;

Try it

Output:

 warehouse_id |        location
--------------+-------------------------
            1 | (37.650971,-122.398658)

You cannot use write (location).latitude as an assignment target in the SET clause.

You use parenthesized composite syntax when you want to read a field from an expression; For the assignment target, you use:

composite_column.field_name

To update all fields of the location column at once, you can assign a new composite to the column:

UPDATE warehouse_locations
SET
  location = ROW (37.650971, -122.398658)
WHERE
  warehouse_id = 1
RETURNING *;

Try it

Output:

 warehouse_id |        location
--------------+-------------------------
            1 | (37.650971,-122.398658)

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 *;

Try it

Output:

 warehouse_id |        location
--------------+-------------------------
            1 | (37.650971,-122.398658)

Inspecting fields of a composite type #

You can query the information_schema.attributes view to inspect the fields of a standalone composite type.

For example, the following query returns the fields of the coordinate type in their declared order:

SELECT
  attribute_name,
  data_type,
  attribute_udt_schema,
  attribute_udt_name,
  ordinal_position
FROM
  information_schema.attributes
WHERE
  udt_schema = 'public'
  AND udt_name = 'coordinate'
ORDER BY
  ordinal_position;

In this query:

  • attribute_name is the name of the field.
  • data_type is the general SQL data type of the field.
  • attribute_udt_schema and attribute_udt_name identify the underlying PostgreSQL type.
  • ordinal_position is the field’s position in the composite type.

It’s a good practice to include udt_schema in the WHERE clause because different schemas can contain types with the same name.

The information_schema.attributes view provides information about fields of composite data types. To inspect ordinary table columns, you use the information_schema.columns view instead.

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;

Try it

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

This is a column-level NOT NULL constraint on the location column. It prevents the complete column value from being a null composite. But it does not independently make latitude and longitude non-null.

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

INSERT INTO warehouse_locations(warehouse_id, location)
VALUES(3, NULL);

Try it

Error:

null value in column "location" of relation "warehouse_locations" violates not-null constraint

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));

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;

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
);

Try it

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

ALTER TABLE warehouse_locations
ALTER COLUMN location TYPE coordinate_domain;

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));

Try it

It will result in an error as expected:

ERROR:  value for domain coordinate_domain violates check constraint "coordinate_domain_check"

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
);

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 defines a structured value containing named fields.
  • Use CREATE TYPE ... AS (...) to create a standalone composite type.
  • Use ROW(...) to construct a composite value.
  • Use (composite_expression).field_name to read an individual field.
  • Use (composite_expression).* to expand all fields.
  • Use composite_column.field_name in an UPDATE SET assignment target.
  • Query information_schema.attributes to inspect the fields of a standalone composite type.
  • A column-level NOT NULL constraint prevents the entire composite column from being null, but it does not make each field non-null.
  • Use a domain over a composite type when reusable field validation is required.
  • Every table has an automatically created composite row type, but the table’s constraints are not carried as constraints on that type outside the table.

Quiz #

Quiz

PostgreSQL Composite Types

7 questions

Learn about PostgreSQL composite types and their applications.

Was this helpful?