0
# PostgreSQL-Specific Types
1

2
This document covers PostgreSQL-specific data types including arrays, geometric types, JSON, hstore, and custom types.
3

4
## Capabilities
5

6
### PGobject Base Class
7

8
Base class for PostgreSQL custom types.
9

10
```java { .api }
11
package org.postgresql.util;
12

13
import java.io.Serializable;
14

15
/**
16
 * Base class for PostgreSQL custom data types.
17
 * Extend this class to create handlers for custom types.
18
 */
19
public class PGobject implements Serializable, Cloneable {
20
    /**
21
     * Sets the PostgreSQL type name for this object.
22
     *
23
     * @param type PostgreSQL type name (e.g., "int4", "json", "point")
24
     */
25
    public void setType(String type);
26

27
    /**
28
     * Gets the PostgreSQL type name.
29
     *
30
     * @return Type name
31
     */
32
    public String getType();
33

34
    /**
35
     * Sets the value from its PostgreSQL string representation.
36
     *
37
     * @param value String value
38
     * @throws SQLException if value cannot be parsed
39
     */
40
    public void setValue(String value) throws SQLException;
41

42
    /**
43
     * Gets the PostgreSQL string representation of this value.
44
     *
45
     * @return String representation
46
     */
47
    public String getValue();
48

49
    /**
50
     * Checks if this object represents SQL NULL.
51
     *
52
     * @return true if NULL
53
     */
54
    public boolean isNull();
55

56
    @Override
57
    public boolean equals(Object obj);
58

59
    @Override
60
    public Object clone() throws CloneNotSupportedException;
61

62
    @Override
63
    public String toString();
64
}
65
```
66

67
**Usage Example:**
68

69
```java
70
import org.postgresql.util.PGobject;
71
import java.sql.*;
72

73
// Example: Working with JSON type
74
public class JSONExample {
75
    public static void insertJSON(Connection conn, String jsonData) throws SQLException {
76
        String sql = "INSERT INTO documents (data) VALUES (?)";
77

78
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
79
            PGobject jsonObject = new PGobject();
80
            jsonObject.setType("json");
81
            jsonObject.setValue(jsonData);
82

83
            pstmt.setObject(1, jsonObject);
84
            pstmt.executeUpdate();
85
        }
86
    }
87

88
    public static String retrieveJSON(Connection conn, int id) throws SQLException {
89
        String sql = "SELECT data FROM documents WHERE id = ?";
90

91
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
92
            pstmt.setInt(1, id);
93

94
            try (ResultSet rs = pstmt.executeQuery()) {
95
                if (rs.next()) {
96
                    PGobject jsonObject = (PGobject) rs.getObject("data");
97
                    return jsonObject.getValue();
98
                }
99
            }
100
        }
101
        return null;
102
    }
103
}
104
```
105

106
### PostgreSQL Arrays
107

108
Native support for PostgreSQL array types.
109

110
```java { .api }
111
import java.sql.Array;
112
import java.sql.Connection;
113
import java.sql.ResultSet;
114
import java.sql.SQLException;
115
import java.util.Map;
116

117
/**
118
 * PostgreSQL Array interface (java.sql.Array).
119
 */
120
public interface Array {
121
    /**
122
     * Gets the SQL type name of array elements.
123
     *
124
     * @return Base type name (e.g., "integer", "text")
125
     * @throws SQLException if type cannot be determined
126
     */
127
    String getBaseTypeName() throws SQLException;
128

129
    /**
130
     * Gets the JDBC type code of array elements.
131
     *
132
     * @return JDBC type code from java.sql.Types
133
     * @throws SQLException if type cannot be determined
134
     */
135
    int getBaseType() throws SQLException;
136

137
    /**
138
     * Returns the array contents as a Java array.
139
     *
140
     * @return Java array (e.g., Integer[], String[])
141
     * @throws SQLException if array cannot be retrieved
142
     */
143
    Object getArray() throws SQLException;
144

145
    /**
146
     * Returns the array contents with custom type mapping.
147
     *
148
     * @param map Type mapping
149
     * @return Mapped array
150
     * @throws SQLException if retrieval fails
151
     */
152
    Object getArray(Map<String,Class<?>> map) throws SQLException;
153

154
    /**
155
     * Returns a subset of the array.
156
     *
157
     * @param index Starting index (1-based)
158
     * @param count Number of elements
159
     * @return Java array
160
     * @throws SQLException if retrieval fails
161
     */
162
    Object getArray(long index, int count) throws SQLException;
163

164
    /**
165
     * Returns array as ResultSet.
166
     * Each row has two columns: INDEX (long) and VALUE (Object).
167
     *
168
     * @return ResultSet representation
169
     * @throws SQLException if conversion fails
170
     */
171
    ResultSet getResultSet() throws SQLException;
172

173
    /**
174
     * Frees array resources.
175
     * Array cannot be used after this call.
176
     *
177
     * @throws SQLException if free fails
178
     */
179
    void free() throws SQLException;
180
}
181
```
182

183
**Usage Examples:**
184

185
```java
186
import org.postgresql.PGConnection;
187
import java.sql.*;
188

189
// Example 1: Insert array
190
public class ArrayInsertExample {
191
    public static void insertArray(Connection conn) throws SQLException {
192
        // Create array from Java array
193
        String[] tags = {"java", "postgresql", "jdbc"};
194
        Array sqlArray = conn.createArrayOf("text", tags);
195

196
        String sql = "INSERT INTO articles (title, tags) VALUES (?, ?)";
197
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
198
            pstmt.setString(1, "My Article");
199
            pstmt.setArray(2, sqlArray);
200
            pstmt.executeUpdate();
201
        }
202

203
        sqlArray.free();
204
    }
205

206
    // Integer array
207
    public static void insertIntArray(Connection conn) throws SQLException {
208
        Integer[] numbers = {1, 2, 3, 4, 5};
209
        Array sqlArray = conn.createArrayOf("integer", numbers);
210

211
        String sql = "INSERT INTO data (values) VALUES (?)";
212
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
213
            pstmt.setArray(1, sqlArray);
214
            pstmt.executeUpdate();
215
        }
216

217
        sqlArray.free();
218
    }
219
}
220

221
// Example 2: Retrieve array
222
public class ArrayRetrieveExample {
223
    public static String[] getArray(Connection conn, int id) throws SQLException {
224
        String sql = "SELECT tags FROM articles WHERE id = ?";
225

226
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
227
            pstmt.setInt(1, id);
228

229
            try (ResultSet rs = pstmt.executeQuery()) {
230
                if (rs.next()) {
231
                    Array sqlArray = rs.getArray("tags");
232
                    String[] tags = (String[]) sqlArray.getArray();
233
                    sqlArray.free();
234
                    return tags;
235
                }
236
            }
237
        }
238
        return null;
239
    }
240
}
241

242
// Example 3: Primitive arrays (PostgreSQL extension)
243
public class PrimitiveArrayExample {
244
    public static void insertPrimitiveArray(Connection conn) throws SQLException {
245
        // PostgreSQL driver supports primitive arrays
246
        PGConnection pgConn = conn.unwrap(PGConnection.class);
247

248
        int[] primitiveInts = {1, 2, 3, 4, 5};
249
        Array sqlArray = pgConn.createArrayOf("integer", primitiveInts);
250

251
        String sql = "INSERT INTO data (values) VALUES (?)";
252
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
253
            pstmt.setArray(1, sqlArray);
254
            pstmt.executeUpdate();
255
        }
256

257
        sqlArray.free();
258
    }
259
}
260

261
// Example 4: Multi-dimensional arrays
262
public class MultiDimensionalArrayExample {
263
    public static void insert2DArray(Connection conn) throws SQLException {
264
        Integer[][] matrix = {
265
            {1, 2, 3},
266
            {4, 5, 6},
267
            {7, 8, 9}
268
        };
269

270
        Array sqlArray = conn.createArrayOf("integer", matrix);
271

272
        String sql = "INSERT INTO matrices (data) VALUES (?)";
273
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
274
            pstmt.setArray(1, sqlArray);
275
            pstmt.executeUpdate();
276
        }
277

278
        sqlArray.free();
279
    }
280
}
281
```
282

283
### Geometric Types
284

285
PostgreSQL geometric types for spatial data.
286

287
```java { .api }
288
package org.postgresql.geometric;
289

290
import org.postgresql.util.PGobject;
291
import java.sql.SQLException;
292

293
/**
294
 * Point type: (x, y)
295
 */
296
public class PGpoint extends PGobject {
297
    /**
298
     * The X coordinate.
299
     */
300
    public double x;
301

302
    /**
303
     * The Y coordinate.
304
     */
305
    public double y;
306

307
    /**
308
     * Indicates whether the value represents null::point.
309
     */
310
    public boolean isNull;
311

312
    /**
313
     * Creates an empty point.
314
     */
315
    public PGpoint();
316

317
    /**
318
     * Creates a point with specified coordinates.
319
     * @param x X coordinate
320
     * @param y Y coordinate
321
     */
322
    public PGpoint(double x, double y);
323

324
    /**
325
     * Creates a point from its string representation.
326
     * @param value String representation (e.g., "(1.0,2.0)")
327
     */
328
    public PGpoint(String value) throws SQLException;
329

330
    @Override
331
    public void setValue(String value) throws SQLException;
332
    @Override
333
    public boolean equals(Object obj);
334
    @Override
335
    public String toString();
336
}
337

338
/**
339
 * Box type: rectangle defined by two opposite corners
340
 */
341
public class PGbox extends PGobject {
342
    public PGpoint[] point; // Array of 2 points
343

344
    public PGbox();
345
    public PGbox(PGpoint p1, PGpoint p2);
346
    public PGbox(double x1, double y1, double x2, double y2);
347
    public PGbox(String value) throws SQLException;
348
}
349

350
/**
351
 * Circle type: center point and radius
352
 */
353
public class PGcircle extends PGobject {
354
    public PGpoint center;
355
    public double radius;
356

357
    public PGcircle();
358
    public PGcircle(PGpoint center, double radius);
359
    public PGcircle(double x, double y, double r);
360
    public PGcircle(String value) throws SQLException;
361
}
362

363
/**
364
 * Line type: infinite line (ax + by + c = 0)
365
 */
366
public class PGline extends PGobject {
367
    public double a;
368
    public double b;
369
    public double c;
370

371
    public PGline();
372
    public PGline(double a, double b, double c);
373
    public PGline(double x1, double y1, double x2, double y2);
374
    public PGline(PGpoint p1, PGpoint p2);
375
    public PGline(PGlseg lseg);
376
    public PGline(String value) throws SQLException;
377
}
378

379
/**
380
 * Line segment type: line between two points
381
 */
382
public class PGlseg extends PGobject {
383
    public PGpoint[] point; // Array of 2 endpoints
384

385
    public PGlseg();
386
    public PGlseg(PGpoint p1, PGpoint p2);
387
    public PGlseg(double x1, double y1, double x2, double y2);
388
    public PGlseg(String value) throws SQLException;
389
}
390

391
/**
392
 * Path type: sequence of connected points
393
 */
394
public class PGpath extends PGobject {
395
    public boolean open; // true for open path, false for closed
396
    public PGpoint[] points;
397

398
    public PGpath();
399
    public PGpath(PGpoint[] points, boolean open);
400
    public PGpath(String value) throws SQLException;
401

402
    /**
403
     * Checks if the path is open.
404
     * @return true if path is open, false if closed
405
     */
406
    public boolean isOpen();
407

408
    /**
409
     * Checks if the path is closed.
410
     * @return true if path is closed, false if open
411
     */
412
    public boolean isClosed();
413

414
    /**
415
     * Closes the path.
416
     */
417
    public void closePath();
418

419
    /**
420
     * Opens the path.
421
     */
422
    public void openPath();
423
}
424

425
/**
426
 * Polygon type: closed path
427
 */
428
public class PGpolygon extends PGobject {
429
    public PGpoint[] points;
430

431
    public PGpolygon();
432
    public PGpolygon(PGpoint[] points);
433
    public PGpolygon(String value) throws SQLException;
434
}
435
```
436

437
**Usage Examples:**
438

439
```java
440
import org.postgresql.geometric.*;
441
import java.sql.*;
442

443
// Example: Geometric types
444
public class GeometricExample {
445
    public static void insertPoint(Connection conn) throws SQLException {
446
        PGpoint point = new PGpoint(10.5, 20.3);
447

448
        String sql = "INSERT INTO locations (name, position) VALUES (?, ?)";
449
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
450
            pstmt.setString(1, "Location A");
451
            pstmt.setObject(2, point);
452
            pstmt.executeUpdate();
453
        }
454
    }
455

456
    public static void insertCircle(Connection conn) throws SQLException {
457
        PGpoint center = new PGpoint(0, 0);
458
        PGcircle circle = new PGcircle(center, 5.0);
459

460
        String sql = "INSERT INTO zones (name, area) VALUES (?, ?)";
461
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
462
            pstmt.setString(1, "Central Zone");
463
            pstmt.setObject(2, circle);
464
            pstmt.executeUpdate();
465
        }
466
    }
467

468
    public static PGpoint retrievePoint(Connection conn, int id) throws SQLException {
469
        String sql = "SELECT position FROM locations WHERE id = ?";
470

471
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
472
            pstmt.setInt(1, id);
473

474
            try (ResultSet rs = pstmt.executeQuery()) {
475
                if (rs.next()) {
476
                    return (PGpoint) rs.getObject("position");
477
                }
478
            }
479
        }
480
        return null;
481
    }
482
}
483
```
484

485
### Interval Type
486

487
PostgreSQL interval type for time durations.
488

489
```java { .api }
490
package org.postgresql.util;
491

492
/**
493
 * PostgreSQL interval type.
494
 */
495
public class PGInterval extends PGobject {
496
    private int years;
497
    private int months;
498
    private int days;
499
    private int hours;
500
    private int minutes;
501
    private double seconds;
502

503
    /**
504
     * Creates an empty interval.
505
     */
506
    public PGInterval();
507

508
    /**
509
     * Creates an interval from its string representation.
510
     * @param value String representation (e.g., '3 years 2 mons' or ISO 8601 format)
511
     */
512
    public PGInterval(String value) throws SQLException;
513

514
    /**
515
     * Creates an interval with specified values.
516
     * @param years Years component
517
     * @param months Months component
518
     * @param days Days component
519
     * @param hours Hours component
520
     * @param minutes Minutes component
521
     * @param seconds Seconds component (can include fractional seconds)
522
     */
523
    public PGInterval(int years, int months, int days, int hours, int minutes, double seconds);
524

525
    /**
526
     * Sets interval components.
527
     */
528
    public void setValue(int years, int months, int days,
529
                        int hours, int minutes, double seconds);
530

531
    // Getters and setters
532
    public int getYears();
533
    public void setYears(int years);
534

535
    public int getMonths();
536
    public void setMonths(int months);
537

538
    public int getDays();
539
    public void setDays(int days);
540

541
    public int getHours();
542
    public void setHours(int hours);
543

544
    public int getMinutes();
545
    public void setMinutes(int minutes);
546

547
    public double getSeconds();
548
    public void setSeconds(double seconds);
549

550
    @Override
551
    public void setValue(String value) throws SQLException;
552
    @Override
553
    public String getValue();
554
}
555
```
556

557
**Usage Example:**
558

559
```java
560
// Example: Interval type
561
public class IntervalExample {
562
    public static void insertInterval(Connection conn) throws SQLException {
563
        PGInterval interval = new PGInterval();
564
        interval.setDays(5);
565
        interval.setHours(12);
566
        interval.setMinutes(30);
567

568
        String sql = "INSERT INTO tasks (name, duration) VALUES (?, ?)";
569
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
570
            pstmt.setString(1, "Task A");
571
            pstmt.setObject(2, interval);
572
            pstmt.executeUpdate();
573
        }
574
    }
575
}
576
```
577

578
### Money Type
579

580
PostgreSQL money type.
581

582
```java { .api }
583
package org.postgresql.util;
584

585
/**
586
 * PostgreSQL money type.
587
 */
588
public class PGmoney extends PGobject {
589
    /**
590
     * The monetary value as a double.
591
     */
592
    public double val;
593

594
    /**
595
     * Indicates whether the value represents null::money.
596
     */
597
    public boolean isNull;
598

599
    /**
600
     * Creates an empty money object.
601
     */
602
    public PGmoney();
603

604
    /**
605
     * Creates a money object with the specified numeric value.
606
     * @param value Numeric value
607
     */
608
    public PGmoney(double value);
609

610
    /**
611
     * Creates a money object from string representation.
612
     * @param value String representation (e.g., "$123.45" or "($50.00)" for negative)
613
     */
614
    public PGmoney(String value) throws SQLException;
615

616
    /**
617
     * Sets value from string representation.
618
     * @param value String representation of money value
619
     */
620
    @Override
621
    public void setValue(String value) throws SQLException;
622

623
    /**
624
     * Returns string representation of the money value.
625
     * @return String in format "$123.45" or "-$50.00"
626
     */
627
    @Override
628
    public String getValue();
629
}
630
```
631

632
### HStore Type
633

634
Key-value store type.
635

636
```java { .api }
637
package org.postgresql.util;
638

639
import java.util.Map;
640

641
/**
642
 * Converter for PostgreSQL hstore type.
643
 */
644
public class HStoreConverter {
645
    /**
646
     * Parses hstore string to Map.
647
     *
648
     * @param s hstore string
649
     * @return Map of key-value pairs
650
     * @throws SQLException if parsing fails
651
     */
652
    public static Map<String, String> fromString(String s) throws SQLException;
653

654
    /**
655
     * Converts Map to hstore string.
656
     *
657
     * @param m Map of key-value pairs
658
     * @return hstore string
659
     */
660
    public static String toString(Map<String, String> m);
661
}
662
```
663

664
**Usage Example:**
665

666
```java
667
import org.postgresql.util.HStoreConverter;
668
import org.postgresql.util.PGobject;
669
import java.util.HashMap;
670
import java.util.Map;
671
import java.sql.*;
672

673
// Example: HStore type
674
public class HStoreExample {
675
    public static void insertHStore(Connection conn) throws SQLException {
676
        Map<String, String> attributes = new HashMap<>();
677
        attributes.put("color", "red");
678
        attributes.put("size", "large");
679
        attributes.put("material", "cotton");
680

681
        String hstoreString = HStoreConverter.toString(attributes);
682

683
        PGobject hstore = new PGobject();
684
        hstore.setType("hstore");
685
        hstore.setValue(hstoreString);
686

687
        String sql = "INSERT INTO products (name, attributes) VALUES (?, ?)";
688
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
689
            pstmt.setString(1, "Product A");
690
            pstmt.setObject(2, hstore);
691
            pstmt.executeUpdate();
692
        }
693
    }
694

695
    public static Map<String, String> retrieveHStore(Connection conn, int id)
696
            throws SQLException {
697
        String sql = "SELECT attributes FROM products WHERE id = ?";
698

699
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
700
            pstmt.setInt(1, id);
701

702
            try (ResultSet rs = pstmt.executeQuery()) {
703
                if (rs.next()) {
704
                    PGobject hstore = (PGobject) rs.getObject("attributes");
705
                    return HStoreConverter.fromString(hstore.getValue());
706
                }
707
            }
708
        }
709
        return null;
710
    }
711
}
712
```
713

714
### UUID Type
715

716
Native UUID support.
717

718
```java
719
import java.util.UUID;
720
import java.sql.*;
721

722
// Example: UUID type
723
public class UUIDExample {
724
    public static void insertUUID(Connection conn) throws SQLException {
725
        UUID uuid = UUID.randomUUID();
726

727
        String sql = "INSERT INTO entities (id, name) VALUES (?, ?)";
728
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
729
            pstmt.setObject(1, uuid);
730
            pstmt.setString(2, "Entity A");
731
            pstmt.executeUpdate();
732
        }
733
    }
734

735
    public static UUID retrieveUUID(Connection conn, String name) throws SQLException {
736
        String sql = "SELECT id FROM entities WHERE name = ?";
737

738
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
739
            pstmt.setString(1, name);
740

741
            try (ResultSet rs = pstmt.executeQuery()) {
742
                if (rs.next()) {
743
                    return rs.getObject("id", UUID.class);
744
                }
745
            }
746
        }
747
        return null;
748
    }
749
}
750
```
751

752
### Custom Type Registration
753

754
Registering custom type handlers.
755

756
```java
757
import org.postgresql.PGConnection;
758
import org.postgresql.util.PGobject;
759
import java.sql.*;
760

761
// Example: Custom type
762
public class CustomTypeExample {
763
    // Define custom type class
764
    public static class MyCustomType extends PGobject {
765
        private String value1;
766
        private int value2;
767

768
        @Override
769
        public void setValue(String value) throws SQLException {
770
            // Parse PostgreSQL representation
771
            String[] parts = value.replaceAll("[()]", "").split(",");
772
            this.value1 = parts[0];
773
            this.value2 = Integer.parseInt(parts[1]);
774
        }
775

776
        @Override
777
        public String getValue() {
778
            // Return PostgreSQL representation
779
            return "(" + value1 + "," + value2 + ")";
780
        }
781
    }
782

783
    // Register custom type
784
    public static void registerCustomType(Connection conn) throws SQLException {
785
        PGConnection pgConn = conn.unwrap(PGConnection.class);
786
        pgConn.addDataType("mytype", MyCustomType.class);
787
    }
788

789
    // Use custom type
790
    public static void useCustomType(Connection conn) throws SQLException {
791
        registerCustomType(conn);
792

793
        MyCustomType custom = new MyCustomType();
794
        custom.setType("mytype");
795
        custom.setValue("(test,42)");
796

797
        String sql = "INSERT INTO custom_table (data) VALUES (?)";
798
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
799
            pstmt.setObject(1, custom);
800
            pstmt.executeUpdate();
801
        }
802
    }
803
}
804
```
805

806
### Type Mapping Summary
807

808
| PostgreSQL Type | Java Type | Method | Notes |
809
|----------------|-----------|--------|-------|
810
| integer[] | Integer[] | createArrayOf("integer", array) | Arrays |
811
| text[] | String[] | createArrayOf("text", array) | Arrays |
812
| point | PGpoint | setObject(PGpoint) | Geometric |
813
| box | PGbox | setObject(PGbox) | Geometric |
814
| circle | PGcircle | setObject(PGcircle) | Geometric |
815
| polygon | PGpolygon | setObject(PGpolygon) | Geometric |
816
| interval | PGInterval | setObject(PGInterval) | Duration |
817
| money | PGmoney | setObject(PGmoney) | Currency |
818
| json/jsonb | String/PGobject | getString() or PGobject | JSON |
819
| hstore | Map<String,String> | HStoreConverter | Key-value |
820
| uuid | UUID | setObject(UUID) | UUID |
821
| custom types | PGobject subclass | setObject() | Extensible |
822