Custom Types

Handle custom database types including ENUMs, composite types, and user-defined types.

Overview

Custom types vary by database:

  • PostgreSQL: ENUMs, composite types, domains

  • Oracle: Object types, VARRAYs, nested tables

  • SQL Server: User-defined types, table types

PostgreSQL ENUM Types

Issue: ENUM Values Not Preserved

Symptom: Invalid ENUM value errors or type mismatch.

Solution: Use Categorical transformer to preserve ENUM values:

transformations:
  - columns: ["status"]
    params:
      type: categorical_generator

Check ENUM Definition

SELECT enumlabel
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
WHERE t.typname = 'order_status';

PostgreSQL Composite Types

Issue: Composite Type Handling

Symptom: "Cannot handle composite type" errors.

Solution: Transform individual fields within composite types:

# For composite type: address(street text, city text, zip text)
transformations:
  - columns: ["(address).street"]
    params:
      type: address_generator
      column_templates: ["${street_address}"]
  - columns: ["(address).city"]
    params:
      type: address_generator
      column_templates: ["${city}"]

Oracle Object Types

Issue: Object Type Serialization

Symptom: Errors with OBJECT types.

Solution: TDK treats object types as structured data. Ensure JDBC driver supports object types:

data_sources:
  input:
    url: jdbc:oracle:thin:@host:1521/service
    driver_class_name: oracle.jdbc.OracleDriver
    # Enable object type support
    connection_properties:
      oracle.jdbc.objectTypeMap: true

SQL Server User-Defined Types

Issue: UDT Not Recognized

Symptom: "Unknown type" or "UDT not found".

Solution: Check UDT definition and map to base type:

-- Check UDT definition
SELECT name, system_type_id, user_type_id, max_length
FROM sys.types
WHERE is_user_defined = 1;

Transform using base type:

# If UDT 'EmailAddress' is based on VARCHAR(255)
transformations:
  - columns: ["email"]  # column of type EmailAddress
    params:
      type: person_generator
      column_templates: ["${email}"]  # Treat as string

Domain Types (PostgreSQL)

Issue: Domain Constraints Violated

Symptom: "Value violates domain constraint".

Solution: Ensure generated values meet domain constraints:

-- Check domain definition
SELECT typname, typnotnull, typdefault
FROM pg_type
WHERE typtype = 'd';  -- domain type

Use transformers that respect constraints:

# For domain: positive_int AS INTEGER CHECK (VALUE > 0)
transformations:
  - columns: ["quantity"]
    params:
      type: continuous_generator
      min: 1  # Respect domain constraint
      max: 10000
      numeric_type: INT

See Also