Skip to main content

PostgreSQL Type Support

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

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.bpchar(10)  // char(10)

Binary Types​

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

Date/Time Types​

PostgreSQL TypeJava TypeNotes
dateLocalDateDate without time
timeLocalTimeTime without timezone
timetzOffsetTimeTime with timezone
timestampLocalDateTimeDate and time without timezone
timestamptzInstantDate and time with timezone (stored as UTC)
intervalPGIntervalTime duration
val dateType: PgType<LocalDate> = PgTypes.date
val timestamptzType: PgType<Instant> = PgTypes.timestamptz
val intervalType: PgType<PGInterval> = PgTypes.interval

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
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. Foundations JDBC supports both boxed and unboxed array variants:

PostgreSQL TypeJava Type (Boxed)Java Type (Unboxed)
int4[]Integer[]int[]
int8[]Long[]long[]
float4[]Float[]float[]
float8[]Double[]double[]
bool[]Boolean[]boolean[]
text[]String[]-
uuid[]UUID[]-
// Boxed arrays
val intArrayBoxed: PgType<Array<Int>> = PgTypes.int4Array

// Unboxed arrays (more efficient)
val intArrayUnboxed: PgType<IntArray> = PgTypes.int4ArrayUnboxed

// Text arrays
val textArray: PgType<Array<String>> = PgTypes.textArray

// Any type can be made into an array
val uuidArray: PgType<Array<UUID>> = PgTypes.uuidArray

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's 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 for it
val statusType: PgType<Status> = PgTypes.ofEnum("status", Status::valueOf)

Custom Domain Types​

Wrap base types with custom Java types using transform:

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

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

Nullable Types​

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

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