Skip to main content

PostgreSQL type support

Full support for all PostgreSQL data types, including arrays, ranges, geometric types, network types, JSON, composites, and enums.

Setting search_path​

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.

Numeric types​

PostgreSQL TypeJava TypeNotes
int2 / smallintShort16-bit signed integer
int4 / integerInteger32-bit signed integer
int8 / bigintLong64-bit signed integer
float4 / realFloat32-bit IEEE 754
float8 / double precisionDouble64-bit IEEE 754
numeric / decimalBigDecimalArbitrary precision
moneyMoneyCurrency with 2 decimal places
val intType: PgType<Int> = PgTypes.int4
val decimalType: PgType<BigDecimal> = PgTypes.numeric
val moneyType: PgType<Money> = PgTypes.money

Boolean type​

PostgreSQL TypeJava Type
bool / booleanBoolean
val boolType: PgType<Boolean> = PgTypes.bool

String types​

PostgreSQL TypeJava TypeNotes
textStringVariable unlimited length
varchar(n)StringVariable length with limit
bpchar / char(n)StringFixed-length, blank-padded
nameString63-character identifier
val textType: PgType<String> = PgTypes.text
val charType: PgType<String> = PgTypes.bpcharOf(10)  // char(10)

Binary types​

PostgreSQL TypeJava TypeNotes
byteabyte[]Variable-length binary
val bytesType: PgType<ByteArray> = PgTypes.bytea

Date/Time Types​

PostgreSQL TypeJava TypeNotes
dateLocalDateNaive date, no zone
timeLocalTimeNaive time, no zone
timetzOffsetTimeTime with offset (rarely used in practice)
timestampLocalDateTimeNaive timestamp, no zone
timestamptzInstantUTC instant — see note below
intervalPGIntervalTime duration
val dateType: PgType<LocalDate> = PgTypes.date
val timestamptzType: PgType<Instant> = PgTypes.timestamptz
val intervalType: PgType<PGInterval> = PgTypes.interval
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.

UUID type​

PostgreSQL TypeJava Type
uuidjava.util.UUID
val uuidType: PgType<UUID> = PgTypes.uuid

JSON types​

PostgreSQL TypeJava TypeNotes
jsonJsonStored as-is, validated on input
jsonbJsonbBinary format, indexed, normalized
Not String — wrapper types

PgTypes.json is PgType<Json> and PgTypes.jsonb is PgType<Jsonb>. These are not String — they are single-field wrapper records (record Json(String value), record Jsonb(String value)) from dev.typr.foundations.data. This means your record fields must be typed Jsonb/Json, not String.

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:

import dev.typr.foundations.data.Jsonb;

new Jsonb("{\"ok\":true}")     // java
import dev.typr.foundations.data.Jsonb

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.

val jsonType: PgType<Json> = PgTypes.json
val jsonbType: PgType<Jsonb> = PgTypes.jsonb

// Parse and use JSON
val data: Json = Json("{\"name\": \"John\"}")

Array types​

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

PostgreSQL TypeJava Type
int4[]List<Integer> via int4.array()
int8[]List<Long> via int8.array()
float4[]List<Float> via float4.array()
float8[]List<Double> via float8.array()
bool[]List<Boolean> via bool.array()
text[]List<String> via text.array()
uuid[]List<UUID> via uuid.array()

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>>.

// Any scalar type can be made into a PostgreSQL array via `.array()`.
val intArray: PgType<List<Int>> = PgTypes.int4.array()
val textArray: PgType<List<String>> = PgTypes.text.array()
val uuidArray: PgType<List<UUID>> = PgTypes.uuid.array()

// Multi-dimensional arrays compose: `int4[][]` in SQL.
val intMatrix: PgType<List<List<Int>>> = PgTypes.int4.array().array()

Composite types​

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.

Range types​

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

PostgreSQL TypeJava TypeElement Type
int4rangeRange<Integer>Integer
int8rangeRange<Long>Long
numrangeRange<BigDecimal>BigDecimal
daterangeRange<LocalDate>LocalDate
tsrangeRange<LocalDateTime>LocalDateTime
tstzrangeRange<Instant>Instant
val intRangeType: PgType<Range<Int>> = PgTypes.int4range
val dateRangeType: PgType<Range<LocalDate>> = PgTypes.daterange

// Create ranges with explicit bounds
val range: Range<Int> = Range.int4(
    RangeBoundClosed(1),
    RangeBoundClosed(10)
)  // [1, 11) after normalization

// Check containment
val contains: Boolean = range.contains(5)  // true

Geometric types​

PostgreSQL geometric types for 2D shapes:

PostgreSQL TypeJava TypeDescription
pointPGpoint(x, y) coordinate
linePGlineInfinite line
lsegPGlsegLine segment
boxPGboxRectangular box
pathPGpathOpen or closed path
polygonPGpolygonClosed polygon
circlePGcircleCircle with center and radius
val pointType: PgType<PGpoint> = PgTypes.point
val circleType: PgType<PGcircle> = PgTypes.circle
val polygonType: PgType<PGpolygon> = PgTypes.polygon

// Create geometric objects
val point: PGpoint = PGpoint(1.0, 2.0)
val circle: PGcircle = PGcircle(point, 5.0)

Network Types​

Types for storing network addresses:

PostgreSQL TypeJava TypeDescription
inetInetIPv4 or IPv6 host address
cidrCidrIPv4 or IPv6 network
macaddrMacaddrMAC address (6 bytes)
macaddr8Macaddr8MAC address (8 bytes, EUI-64)
val inetType: PgType<Inet> = PgTypes.inet
val cidrType: PgType<Cidr> = PgTypes.cidr

val addr: Inet = Inet("192.168.1.1/24")

Text Search Types​

Full-text search types:

PostgreSQL TypeJava TypeDescription
tsvectorTsvectorText search document
tsqueryTsqueryText search query
// Text search types are available via PgTypes
// Note: tsvector and tsquery have specialized handling
val textType: PgType<String> = PgTypes.text

XML Type​

PostgreSQL TypeJava Type
xmlXml
val xmlType: PgType<Xml> = PgTypes.xml
val doc: Xml = Xml("<root><child>text</child></root>")

Other Special Types​

PostgreSQL TypeJava TypeDescription
hstoreMap<String, String>Key-value store
vectorVectorpgvector extension
recordRecordAnonymous composite type
val hstoreType: PgType<Map<String, String>> = PgTypes.hstore
val vectorType: PgType<Vector> = PgTypes.vector

System Types​

Types used internally by PostgreSQL:

PostgreSQL TypeJava TypeDescription
oidLongObject identifier
xidXidTransaction ID
regclassRegclassRelation name/OID
regtypeRegtypeType name/OID
regprocRegprocFunction name/OID

Enum Types​

PostgreSQL enums are mapped to Java enums:

// Define your Kotlin enum
enum class Status { PENDING, ACTIVE, COMPLETED }

// Create a PgType — reified, no arguments beyond the SQL type name
val statusType: PgType<Status> = PgTypes.ofEnum<Status>("status")
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.

Custom Domain Types​

Wrap base types with custom Java types using transform. Useful when you want a typed wrapper on the application side without changing PG's schema:

// Wrapper type
data class Email(val value: String)

// Create PgType from text
val emailType: PgType<Email> = PgTypes.text.transform(::Email, Email::value)

PostgreSQL DOMAIN types​

For an actual CREATE DOMAIN dom AS underlying schema-side type, use asDomain. The two-arg form takes the domain name and a constructor / extractor for the wrapping value type, so the entire DOMAIN-plus-wrapper declaration is one expression:

// PG schema:  CREATE DOMAIN person_name AS varchar(100);
data class Name(val value: String) {
    companion object {
        val pgType: PgType<Name> =
            PgTypes.text.asDomain("person_name", ::Name, Name::value)
    }
}

asDomain renames the typename for SQL rendering, registers the underlying typename as a query-analyzer alias (PG JDBC resolves domains to their base type in ResultSetMetaData), and configures the array codec to text-parse so domain arrays decode correctly. It also covers domain over enum, domain over composite, etc. — the underlying codec is reused.

Arrays of a domain "just work" — wrap once at the scalar level and .array() carries the wrapper through. No list-level bijection is needed:

// Wrap once at the scalar level — the array codec carries the wrapper through
// .array(), so no list-level bijection is needed.
data class Name(val value: String) {
    companion object {
        val pgType: PgType<Name> =
            PgTypes.text.asDomain("person_name", ::Name, Name::value)
        val pgArrayType: PgType<List<Name>> = pgType.array()
    }
}
Equality on domain-typed columns

PG does not always define operators on a domain in its own right (e.g. domain-over-enum has no operator class — operators are bound to the enum's OID). For columns where you compare on the domain, cast to the underlying: WHERE v::underlying = $1::underlying. Read/write through the codec is unaffected.

Nullable Types​

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

val notNull: PgType<Int> = PgTypes.int4
val nullable: PgType<Int?> = PgTypes.int4.opt()