Reducing Boilerplate Code through PostgreSQL Composite Types

PostgreSQL is a powerful and flexible open-source relational database management system renowned for its vast feature set. Among these, the ability to define custom composite types is particularly useful. Introduced in Postgres 11, these user-defined data structures combine multiple fields of varying types into a single logical entity. This blog post takes a closer look at the benefits of using composite types in PostgreSQL and explains how to include them in your database schema.

Create a Composite Type

A composite type in PostgreSQL can be defined using the CREATE TYPE statement, followed by the name of the type and a list of its component fields and types. For instance, an audit type may be defined as follows:

1CREATE TYPE AUDIT AS (
2    created_at TIMESTAMPTZ,
3    created_by UUID,
4    updated_at TIMESTAMPTZ,
5    updated_by UUID
6);

Using the Composite Type for Table Creation

Once the composite type is defined, it can be used in your database schema just like any other data type. Below is an example of creating a table that includes a column of the composite type, audit:

1CREATE TABLE customer (
2    id UUID PRIMARY KEY,
3    audit AUDIT
4);

Insert and Query Data

To insert data into a table that includes a composite type, use the ROW constructor syntax, as illustrated:

1INSERT INTO customer(id, audit.created_at, audit.created_by)
2VALUES (gen_random_uuid(), NOW(), '36765812-fa07-4227-bb90-e2b6ff00da89');

When querying data from a table that includes a composite type, the dot notation allows access to individual fields of the type:

1SELECT (audit).created_at, (audit).created_by FROM customer;

Note: Parenthesis must be used around the composite property, otherwise PostgreSQL tries to interpret it as a separate table.

Define Constraints using DOMAIN Types

Composite Types do not inherently support constraints. However, you can create a DOMAIN type that includes constraints:

1CREATE DOMAIN AUDIT_DOMAIN AS AUDIT
2CHECK (
3    (value).created_at IS NOT NULL AND
4    (value).created_by IS NOT NULL
5);

Now, use the DOMAIN type rather than the original composite type:

1CREATE TABLE customer (
2    id UUID PRIMARY KEY,
3    audit AUDIT_DOMAIN
4);

Any inserts or updates that violate this constraint will fail:

1INSERT INTO customer(id, audit.created_at)
2VALUES (gen_random_uuid(), NOW());
3
4-- Results in: [23514] ERROR: value for domain audit_domain violates check constraint "audit_domain_check"

Using Composite Types in Hibernate

The upcoming Hibernate 6.2 release will introduce a new @Struct annotation to facilitate the use of Composite Types:

1@Embeddable
2@Struct(name = "AUDIT_DOMAIN")
3public class Audit {
4
5    @NotNull
6    private ZonedDateTime createdAt;
7    @NotNull
8    private UUID createdBy;
9    
10    private ZonedDateTime updatedAt;
11    private UUID updatedBy;
12}

For additional information, see: Composite type with Hibernate.