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

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

  static public 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);
  }

  static public 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), DB2, and SQLite don't support composite types. Alternatives:

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