Skip to main content

Struct Types

A struct type (or composite type) combines multiple fields into a single structured value. These represent things like addresses, coordinates, or any multi-field value object.

Database Support​

DatabaseMechanismNotes
PostgreSQLComposite TypesUser-defined record types
OracleOBJECT TypesFull object-relational types with methods
DuckDBSTRUCTInline composite types
SQL ServerTable TypesFor table-valued parameters only
MariaDB/MySQLNoneNo composite type support
DB2NoneNo composite type support

PostgreSQL Composite Types​

PostgreSQL composite types are defined at the schema level:

CREATE TYPE contact_info AS (
  phone text,
  mobile text,
  emergency_contact text,
  emergency_phone text
);

Typr generates a record type with full read/write support:

ContactInfo
/** PostgreSQL composite type: showcase.contact_info */
public record ContactInfo(
    Optional<String> phone,
    Optional<String> mobile,
    Optional<String> emergencyContact,
    Optional<String> emergencyPhone) {
  public ContactInfo withPhone(Optional<String> phone) {
    return new ContactInfo(phone, mobile, emergencyContact, emergencyPhone);
  }

  public ContactInfo withMobile(Optional<String> mobile) {
    return new ContactInfo(phone, mobile, emergencyContact, emergencyPhone);
  }

  public ContactInfo withEmergencyContact(Optional<String> emergencyContact) {
    return new ContactInfo(phone, mobile, emergencyContact, emergencyPhone);
  }

  public ContactInfo withEmergencyPhone(Optional<String> emergencyPhone) {
    return new ContactInfo(phone, mobile, emergencyContact, emergencyPhone);
  }

  public static PgType<ContactInfo> pgType =
      PgTypes.compositeOf(
          "showcase.contact_info",
          RowCodec.<ContactInfo>namedBuilder()
              .field("phone", PgTypes.text.opt(), v -> v.phone())
              .field("mobile", PgTypes.text.opt(), v -> v.mobile())
              .field("emergencyContact", PgTypes.text.opt(), v -> v.emergencyContact())
              .field("emergencyPhone", PgTypes.text.opt(), v -> v.emergencyPhone())
              .build(ContactInfo::new));

  public static PgType<List<ContactInfo>> pgTypeArray = pgType.array();
}

Oracle OBJECT Types​

Oracle OBJECT types represent a single structured value with named fields - similar to a record or struct:

CREATE TYPE contact_info_t AS OBJECT (
  phone VARCHAR2(20),
  mobile VARCHAR2(20),
  emergency_contact VARCHAR2(100),
  emergency_phone VARCHAR2(20)
);
ContactInfoT
/** Oracle Object Type: showcase.contact_info_t */
public record ContactInfoT(
    String phone, String mobile, String emergencyContact, String emergencyPhone) {
  public ContactInfoT withPhone(String phone) {
    return new ContactInfoT(phone, mobile, emergencyContact, emergencyPhone);
  }

  public ContactInfoT withMobile(String mobile) {
    return new ContactInfoT(phone, mobile, emergencyContact, emergencyPhone);
  }

  public ContactInfoT withEmergencyContact(String emergencyContact) {
    return new ContactInfoT(phone, mobile, emergencyContact, emergencyPhone);
  }

  public ContactInfoT withEmergencyPhone(String emergencyPhone) {
    return new ContactInfoT(phone, mobile, emergencyContact, emergencyPhone);
  }

  public static OracleType<ContactInfoT> oracleType =
      OracleTypes.compositeOf(
          "contact_info_t",
          RowCodec.<ContactInfoT>namedBuilder()
              .field("phone", OracleTypes.varchar2, ContactInfoT::phone)
              .field("mobile", OracleTypes.varchar2, ContactInfoT::mobile)
              .field("emergency_contact", OracleTypes.varchar2, ContactInfoT::emergencyContact)
              .field("emergency_phone", OracleTypes.varchar2, ContactInfoT::emergencyPhone)
              .build(ContactInfoT::new));
}
tip

For Oracle collection types (VARRAYs and Nested Tables), see Collection Types.

DuckDB STRUCT​

DuckDB supports inline composite types defined in column definitions:

CREATE TABLE employee (
  contact_info STRUCT(
    phone VARCHAR,
    mobile VARCHAR,
    emergency_contact VARCHAR,
    emergency_phone VARCHAR
  )
);

Typr generates a record type for each distinct struct:

EmployeeContactInfo
/** DuckDB STRUCT type: EmployeeContactInfo */
public record EmployeeContactInfo(
    String phone, String mobile, String emergencyContact, String emergencyPhone) {
  public EmployeeContactInfo withPhone(String phone) {
    return new EmployeeContactInfo(phone, mobile, emergencyContact, emergencyPhone);
  }

  public EmployeeContactInfo withMobile(String mobile) {
    return new EmployeeContactInfo(phone, mobile, emergencyContact, emergencyPhone);
  }

  public EmployeeContactInfo withEmergencyContact(String emergencyContact) {
    return new EmployeeContactInfo(phone, mobile, emergencyContact, emergencyPhone);
  }

  public EmployeeContactInfo withEmergencyPhone(String emergencyPhone) {
    return new EmployeeContactInfo(phone, mobile, emergencyContact, emergencyPhone);
  }

  public static DuckDbType<EmployeeContactInfo> duckDbType =
      DuckDbTypes.compositeOf(
          "EmployeeContactInfo",
          RowCodec.<EmployeeContactInfo>namedBuilder()
              .field("phone", DuckDbTypes.varchar, EmployeeContactInfo::phone)
              .field("mobile", DuckDbTypes.varchar, EmployeeContactInfo::mobile)
              .field("emergencyContact", DuckDbTypes.varchar, EmployeeContactInfo::emergencyContact)
              .field("emergencyPhone", DuckDbTypes.varchar, EmployeeContactInfo::emergencyPhone)
              .build(EmployeeContactInfo::new));
}

Databases Without Struct Support​

MariaDB/MySQL, SQL Server (outside of table types), and DB2 don't support composite types. Alternatives:

  • Use separate columns
  • Store as JSON
  • Use multiple related tables