import Snippet from '@site/src/components/Snippet';

Foundations JDBC provides comprehensive support for all PostgreSQL data types, including the many exotic types that make PostgreSQL unique.

Use PgConfig.Builder.currentSchema(...) to set a comma-separated search_path at connection time — handy when your composite types, enums, or tables live in a non-public schema and you want to reference them unqualified in SQL:

var config = PgConfig.builder("host", 5432, "db", "user", "pw")
    .currentSchema("app,public")   // search_path = app, public
    .build();

This maps to PostgreSQL's currentSchema connection property. For single-schema use, ConnectionSettings.schema(...) also works but only accepts one name.

:::note timestamptz does not store a time zone PostgreSQL is explicit on this: for timestamp with time zone, "the value is stored internally as UTC, and the originally stated or assumed time zone is not retained" (from the PostgreSQL docs). The zone only affects how the value is rendered at read-time (always converted to the session TimeZone setting).

Because the column genuinely stores a universal instant — not a zoned value — the library maps it to java.time.Instant. Any zone information must travel alongside the value in a separate column if you need it (same data-modelling approach as Jira, GitHub, and most other systems). Using OffsetDateTime here would suggest the stored value carries an offset, which it does not.

This is the reference mapping for the whole library: DuckDB's TIMESTAMPTZ shares the same semantics and uses the same Instant mapping. SQL Server's DATETIMEOFFSET and Oracle's TIMESTAMP WITH TIME ZONE genuinely preserve offset/zone and therefore map differently — see each dialect's page for details. :::

Json and Jsonb are distinct wrapper records around a String payload, so a single row with both a json and a jsonb column keeps its types straight. Wrap the raw JSON text at the edges:

new Jsonb("{\"ok\":true}")     // java

Jsonb("""{"ok":true}""")        // kotlin

A common first-run surprise is declaring val payload: String on a Kotlin data class and getting actual type is 'String', but 'Jsonb!' was expected — the Kotlin ! just marks a platform type, the fix is the wrap above.

Any PostgreSQL type can be used as an array — call .array() on the element type. The Java representation is always List<T>:

This works for all types — numeric.array(), timestamptz.array(), jsonb.array(), custom enum types, composite types, etc. Multi-dimensional arrays compose: .array().array() produces SQL T[][] with Java type List<List<T>>.

PostgreSQL composite types (row constructors and CREATE TYPE declarations) are built from a RowCodecNamed via compositeOf:

// Ad-hoc composite — for row constructors like (a, b, c) in SQL
PgType<LineItem> lineItemType = PgTypes.compositeOf(
    RowCodec.<LineItem>namedBuilder()
        .field("product_name", PgTypes.text, LineItem::productName)
        .field("quantity", PgTypes.int4, LineItem::quantity)
        .field("unit_price", PgTypes.numeric, LineItem::unitPrice)
        .build(LineItem::new));

// Named composite — for CREATE TYPE declarations (supports writes)
PgType<Address> addressType = PgTypes.compositeOf("address", addressCodec);

// Array of composites — works like any other type
PgType<List<LineItem>> lineItemArrayType = lineItemType.array();

The same RowCodecNamed codec can be reused for flat row queries, composite types, JSON-encoded columns, and query analysis.

PostgreSQL's range types represent intervals of values with inclusive/exclusive bounds:

PostgreSQL's geometric types for 2D shapes:

Types for storing network addresses:

Full-text search types:

Types used internally by PostgreSQL:

PostgreSQL enums are mapped to Java enums:

:::note sqlType must match the CREATE TYPE name (schema-qualified if needed) The first argument to ofEnum(sqlType, ...) is the PostgreSQL type name used to cast bound parameters. Pass exactly the name that appears in CREATE TYPE schema.color AS ENUM(...) — including the schema prefix if the type isn't in search_path. A mismatch produces type "color" does not exist on the first insert. :::

Wrap base types with custom Java types using transform:

Any type can be made nullable using .opt():