0
# Type System and Adaptation
1

2
PostgreSQL type system integration with automatic type conversion, custom type registration, and support for PostgreSQL-specific types like arrays, JSON, ranges, and geometric types.
3

4
## Capabilities
5

6
### Type Information Management
7

8
Core classes for managing PostgreSQL type metadata and registering type information.
9

10
```python { .api }
11
class TypeInfo:
12
    """PostgreSQL type information container"""
13
    
14
    def __init__(
15
        self,
16
        name: str,
17
        oid: int,
18
        array_oid: int,
19
        *,
20
        regtype: str = "",
21
        delimiter: str = ",",
22
        typemod: type = None
23
    ):
24
        """
25
        Create type information object.
26
        
27
        Args:
28
            name: PostgreSQL type name
29
            oid: Type object identifier
30
            array_oid: Array type OID
31
            regtype: Type registration name (defaults to empty string)
32
            delimiter: Array element delimiter (defaults to comma)
33
            typemod: Type modifier class
34
        """
35
    
36
    @property
37
    def name(self) -> str:
38
        """PostgreSQL type name"""
39
    
40
    @property
41
    def oid(self) -> int:
42
        """Type object identifier"""
43
    
44
    @property
45
    def array_oid(self) -> int:
46
        """Array type OID (0 if no array type exists)"""
47
    
48
    @property
49
    def regtype(self) -> str | None:
50
        """Type registration name for PostgreSQL"""
51
    
52
    @property
53
    def delimiter(self) -> str:
54
        """Array element delimiter character"""
55
    
56
    @classmethod
57
    def fetch(
58
        cls,
59
        conn,
60
        name_or_oid: str | int
61
    ) -> TypeInfo:
62
        """
63
        Fetch type information from database.
64
        
65
        Args:
66
            conn: Database connection
67
            name_or_oid: Type name or OID to fetch
68
            
69
        Returns:
70
            TypeInfo object with database metadata
71
        """
72
    
73
    def register(self, context: AdaptContext) -> None:
74
        """
75
        Register this type in adaptation context.
76
        
77
        Args:
78
            context: Adaptation context to register in
79
        """
80

81
class TypesRegistry:
82
    """Registry for PostgreSQL type information and adapters"""
83
    
84
    def get_by_oid(self, oid: int) -> TypeInfo | None:
85
        """
86
        Get type information by OID.
87
        
88
        Args:
89
            oid: PostgreSQL type OID
90
            
91
        Returns:
92
            TypeInfo if found, None otherwise
93
        """
94
    
95
    def get_by_name(self, name: str) -> TypeInfo | None:
96
        """
97
        Get type information by name.
98
        
99
        Args:
100
            name: PostgreSQL type name
101
            
102
        Returns:
103
            TypeInfo if found, None otherwise
104
        """
105
    
106
    def add(self, type_info: TypeInfo) -> None:
107
        """
108
        Add type information to registry.
109
        
110
        Args:
111
            type_info: TypeInfo to add
112
        """
113
```
114

115
### Adapter System
116

117
Core adaptation system for converting between Python objects and PostgreSQL data.
118

119
```python { .api }
120
from typing import Any, Callable, Type
121

122
class AdaptersMap:
123
    """Map of type adapters for connections"""
124
    
125
    @property
126
    def types(self) -> TypesRegistry:
127
        """Associated types registry"""
128
    
129
    def register_loader(
130
        self,
131
        oid: int,
132
        loader: Callable[[bytes], Any]
133
    ) -> None:
134
        """
135
        Register function to load PostgreSQL data to Python.
136
        
137
        Args:
138
            oid: PostgreSQL type OID
139
            loader: Function to convert bytes to Python object
140
        """
141
    
142
    def register_dumper(
143
        self,
144
        cls: Type,
145
        dumper: Callable[[Any], bytes]
146
    ) -> None:
147
        """
148
        Register function to dump Python data to PostgreSQL.
149
        
150
        Args:
151
            cls: Python class to handle
152
            dumper: Function to convert Python object to bytes
153
        """
154
    
155
    def get_loader(self, oid: int) -> Callable[[bytes], Any] | None:
156
        """Get loader function for PostgreSQL type OID"""
157
    
158
    def get_dumper(self, obj: Any) -> Callable[[Any], bytes] | None:
159
        """Get dumper function for Python object"""
160

161
class Transformer:
162
    """Transform query parameters and results"""
163
    
164
    def __init__(self, context: AdaptContext):
165
        """
166
        Create transformer for adaptation context.
167
        
168
        Args:
169
            context: Adaptation context (connection or cursor)
170
        """
171
    
172
    def dump_sequence(
173
        self,
174
        params: Sequence[Any],
175
        format: PyFormat = PyFormat.AUTO
176
    ) -> list[bytes]:
177
        """
178
        Convert Python sequence to PostgreSQL format.
179
        
180
        Args:
181
            params: Python objects to convert
182
            format: Output format (text/binary)
183
            
184
        Returns:
185
            List of converted bytes objects
186
        """
187
    
188
    def load_sequence(
189
        self,
190
        row: Sequence[bytes],
191
        format: PyFormat = PyFormat.AUTO
192
    ) -> list[Any]:
193
        """
194
        Convert PostgreSQL row to Python objects.
195
        
196
        Args:
197
            row: Raw bytes from PostgreSQL
198
            format: Input format (text/binary)
199
            
200
        Returns:
201
            List of converted Python objects
202
        """
203

204
class Dumper:
205
    """Base class for converting Python objects to PostgreSQL format"""
206
    
207
    def __init__(self, cls: type, context: AdaptContext | None = None):
208
        """
209
        Initialize dumper for Python class.
210
        
211
        Args:
212
            cls: Python class this dumper handles
213
            context: Adaptation context (connection info)
214
        """
215
    
216
    def dump(self, obj: Any) -> bytes:
217
        """
218
        Convert Python object to PostgreSQL bytes.
219
        
220
        Args:
221
            obj: Python object to convert
222
            
223
        Returns:
224
            Object serialized as bytes for PostgreSQL
225
        """
226
    
227
    def quote(self, obj: Any) -> bytes:
228
        """
229
        Convert and quote Python object for SQL inclusion.
230
        
231
        Args:
232
            obj: Python object to quote
233
            
234
        Returns:
235
            SQL-safe quoted bytes representation
236
        """
237
    
238
    @property
239
    def oid(self) -> int:
240
        """PostgreSQL type OID this dumper produces"""
241

242
class Loader:
243
    """Base class for converting PostgreSQL data to Python objects"""
244
    
245
    def __init__(self, oid: int, context: AdaptContext | None = None):
246
        """
247
        Initialize loader for PostgreSQL type.
248
        
249
        Args:
250
            oid: PostgreSQL type OID this loader handles
251
            context: Adaptation context (connection info)
252
        """
253
    
254
    def load(self, data: bytes) -> Any:
255
        """
256
        Convert PostgreSQL bytes to Python object.
257
        
258
        Args:
259
            data: Raw bytes from PostgreSQL
260
            
261
        Returns:
262
            Converted Python object
263
        """
264

265
class AdaptContext:
266
    """Context for type adaptation with connection information"""
267
    
268
    def __init__(self, adapters: AdaptersMap | None = None):
269
        """
270
        Create adaptation context.
271
        
272
        Args:
273
            adapters: Adapters map to use (defaults to global)
274
        """
275
    
276
    @property
277
    def adapters(self) -> AdaptersMap:
278
        """Adapters map for this context"""
279
    
280
    @property
281
    def connection(self) -> Connection | None:
282
        """Associated connection (None for global context)"""
283
    
284
    @property
285
    def encoding(self) -> str:
286
        """Character encoding for text conversion"""
287
```
288

289
### Built-in Type Support
290

291
Psycopg provides automatic adaptation for standard Python and PostgreSQL types.
292

293
#### Basic Types
294

295
```python { .api }
296
# Automatic conversions (no explicit registration needed):
297

298
# Python -> PostgreSQL
299
None -> NULL
300
bool -> boolean  
301
int -> integer/bigint
302
float -> double precision
303
str -> text/varchar
304
bytes -> bytea
305
Decimal -> numeric
306
datetime.date -> date
307
datetime.time -> time
308
datetime.datetime -> timestamp
309
datetime.timedelta -> interval
310
uuid.UUID -> uuid
311

312
# PostgreSQL -> Python  
313
NULL -> None
314
boolean -> bool
315
integer/bigint -> int
316
double precision -> float
317
text/varchar -> str
318
bytea -> bytes
319
numeric -> Decimal
320
date -> datetime.date
321
time -> datetime.time  
322
timestamp -> datetime.datetime
323
interval -> datetime.timedelta
324
uuid -> uuid.UUID
325
```
326

327
#### Array Types
328

329
```python { .api }
330
# Array support for all basic types
331
list[int] -> integer[]
332
list[str] -> text[]
333
list[datetime.date] -> date[]
334

335
# Multi-dimensional arrays
336
list[list[int]] -> integer[][]
337

338
# Custom array delimiter support via TypeInfo
339
```
340

341
#### JSON Types
342

343
```python { .api }
344
# JSON/JSONB support
345
dict -> json/jsonb
346
list -> json/jsonb  
347
Any JSON-serializable object -> json/jsonb
348

349
# Custom JSON encoder/decoder registration
350
```
351

352
### Custom Type Registration
353

354
Register custom Python types for automatic conversion to/from PostgreSQL.
355

356
```python { .api }
357
def register_custom_type(
358
    conn,
359
    python_type: Type,
360
    pg_type_name: str,
361
    dumper: Callable[[Any], str],
362
    loader: Callable[[str], Any]
363
) -> None:
364
    """
365
    Register custom type conversion.
366
    
367
    Args:
368
        conn: Database connection
369
        python_type: Python class to register
370
        pg_type_name: PostgreSQL type name
371
        dumper: Function to convert Python object to string
372
        loader: Function to convert string to Python object
373
    """
374
    
375
    # Fetch type info from database
376
    type_info = TypeInfo.fetch(conn, pg_type_name)
377
    
378
    # Register dumper for Python -> PostgreSQL
379
    conn.adapters.register_dumper(python_type, dumper)
380
    
381
    # Register loader for PostgreSQL -> Python
382
    conn.adapters.register_loader(type_info.oid, loader)
383
```
384

385
### Specialized Type Modules
386

387
Psycopg includes specialized modules for PostgreSQL-specific data types.
388

389
#### Array Types
390

391
```python { .api }
392
from psycopg.types import array
393

394
# Array type registration
395
def register_array_type(
396
    conn,
397
    element_type_name: str,
398
    array_type_name: str
399
) -> None:
400
    """Register PostgreSQL array type for automatic handling"""
401

402
# Custom array handling
403
class ArrayDumper:
404
    """Custom array dumper for specialized formatting"""
405
    
406
class ArrayLoader:  
407
    """Custom array loader for specialized parsing"""
408
```
409

410
#### Range Types
411

412
```python { .api }
413
from psycopg.types import range
414

415
class Range:
416
    """PostgreSQL range type representation"""
417
    
418
    def __init__(
419
        self, 
420
        lower=None, 
421
        upper=None, 
422
        bounds="[)", 
423
        empty=False
424
    ):
425
        """
426
        Create range object.
427
        
428
        Args:
429
            lower: Lower bound value
430
            upper: Upper bound value  
431
            bounds: Bound inclusion ("[)", "(]", "[]", "()")
432
            empty: True for empty range
433
        """
434
    
435
    @property
436
    def lower(self) -> Any:
437
        """Lower bound value"""
438
    
439
    @property
440
    def upper(self) -> Any:
441
        """Upper bound value"""
442
    
443
    @property
444
    def bounds(self) -> str:
445
        """Bound inclusion string"""
446
    
447
    @property
448
    def empty(self) -> bool:
449
        """True if range is empty"""
450

451
# Built-in range types
452
DateRange = Range[datetime.date]
453
TimestampRange = Range[datetime.datetime]  
454
NumericRange = Range[Decimal]
455
```
456

457
#### Composite Types
458

459
```python { .api }
460
from psycopg.types import composite
461

462
def register_composite_type(
463
    conn,
464
    type_name: str,
465
    python_class: Type
466
) -> None:
467
    """
468
    Register PostgreSQL composite type with Python class.
469
    
470
    Args:
471
        conn: Database connection
472
        type_name: PostgreSQL composite type name
473
        python_class: Python class to map to
474
    """
475

476
# Custom composite type handling
477
class CompositeLoader:
478
    """Load PostgreSQL composite types to Python objects"""
479
    
480
class CompositeDumper:
481
    """Dump Python objects to PostgreSQL composite types"""
482
```
483

484
#### Enumeration Types
485

486
```python { .api }
487
from psycopg.types import enum
488

489
def register_enum_type(
490
    conn,
491
    enum_type_name: str,
492
    python_enum: Type[Enum]
493
) -> None:
494
    """
495
    Register PostgreSQL enum type with Python Enum.
496
    
497
    Args:
498
        conn: Database connection  
499
        enum_type_name: PostgreSQL enum type name
500
        python_enum: Python Enum class
501
    """
502

503
# Usage with Python enums
504
from enum import Enum
505

506
class Color(Enum):
507
    RED = "red"
508
    GREEN = "green" 
509
    BLUE = "blue"
510

511
register_enum_type(conn, "color", Color)
512
```
513

514
#### Network Address Types
515

516
```python { .api }
517
from psycopg.types import net
518
import ipaddress
519

520
# Automatic conversion
521
ipaddress.IPv4Address -> inet
522
ipaddress.IPv6Address -> inet  
523
ipaddress.IPv4Network -> cidr
524
ipaddress.IPv6Network -> cidr
525
```
526

527
#### UUID Types
528

529
```python { .api }
530
import uuid
531

532
# Automatic UUID conversion
533
uuid.UUID -> uuid
534
# PostgreSQL uuid -> uuid.UUID
535
```
536

537
#### Geometric Types
538

539
```python { .api }
540
from psycopg.types import geometry
541

542
# Point type
543
class Point:
544
    def __init__(self, x: float, y: float): ...
545
    
546
# Line, Circle, Box, Path, Polygon types available
547
```
548

549
### Third-Party Integration
550

551
Integration modules for popular Python libraries.
552

553
#### NumPy Integration
554

555
```python { .api }
556
from psycopg.types import numpy
557

558
# NumPy array <-> PostgreSQL array conversion
559
import numpy as np
560

561
# Automatic conversion when numpy is available
562
np.ndarray -> PostgreSQL array
563
PostgreSQL array -> np.ndarray
564
```
565

566
#### Shapely Integration
567

568
```python { .api }  
569
from psycopg.types import shapely
570

571
# Shapely geometry <-> PostGIS geometry conversion
572
from shapely.geometry import Point, LineString, Polygon
573

574
# Automatic conversion when shapely is available
575
Point -> PostGIS POINT
576
LineString -> PostGIS LINESTRING  
577
Polygon -> PostGIS POLYGON
578
```
579

580
## Advanced Usage Examples
581

582
### Custom Type Registration Example
583

584
```python
585
from decimal import Decimal
586
from psycopg.types import TypeInfo
587

588
class Money:
589
    """Custom money type with currency"""
590
    def __init__(self, amount: Decimal, currency: str = "USD"):
591
        self.amount = amount
592
        self.currency = currency
593
    
594
    def __str__(self):
595
        return f"{self.amount} {self.currency}"
596

597
def register_money_type(conn):
598
    """Register custom money type"""
599
    
600
    # Create dumper (Python -> PostgreSQL)
601
    def dump_money(obj: Money) -> str:
602
        return f"{obj.amount}"  # PostgreSQL money type
603
    
604
    # Create loader (PostgreSQL -> Python)  
605
    def load_money(data: str) -> Money:
606
        # Parse PostgreSQL money format: $12.34
607
        amount_str = data.replace('$', '').replace(',', '')
608
        return Money(Decimal(amount_str))
609
    
610
    # Get type info from database
611
    money_info = TypeInfo.fetch(conn, "money")
612
    
613
    # Register adapters
614
    conn.adapters.register_dumper(Money, dump_money)
615
    conn.adapters.register_loader(money_info.oid, load_money)
616

617
# Usage
618
register_money_type(conn)
619

620
with conn.cursor() as cur:
621
    # Insert Money object
622
    cur.execute(
623
        "INSERT INTO products (name, price) VALUES (%s, %s)",
624
        ("Widget", Money(Decimal("19.99"), "USD"))
625
    )
626
    
627
    # Query returns Money objects
628
    cur.execute("SELECT name, price FROM products")
629
    for name, price in cur:
630
        print(f"{name}: {price}")  # Widget: 19.99 USD
631
```
632

633
### Custom Array Type
634

635
```python
636
from psycopg.types import array, TypeInfo
637

638
class Tag:
639
    """Simple tag object"""
640
    def __init__(self, name: str, color: str = "blue"):
641
        self.name = name
642
        self.color = color
643
    
644
    def __str__(self):
645
        return f"{self.name}:{self.color}"
646

647
def register_tag_array(conn):
648
    """Register tag array type"""
649
    
650
    # First register individual tag type
651
    def dump_tag(obj: Tag) -> str:
652
        return f"{obj.name}:{obj.color}"
653
    
654
    def load_tag(data: str) -> Tag:
655
        parts = data.split(":", 1)
656
        return Tag(parts[0], parts[1] if len(parts) > 1 else "blue")
657
    
658
    # Register tag type (assuming custom PostgreSQL type exists)
659
    tag_info = TypeInfo.fetch(conn, "tag")
660
    conn.adapters.register_dumper(Tag, dump_tag)
661
    conn.adapters.register_loader(tag_info.oid, load_tag)
662
    
663
    # Register tag array type
664
    tag_array_info = TypeInfo.fetch(conn, "_tag")  # Array type usually prefixed with _
665
    
666
    def dump_tag_array(obj_list: list[Tag]) -> str:
667
        return "{" + ",".join(dump_tag(tag) for tag in obj_list) + "}"
668
    
669
    def load_tag_array(data: str) -> list[Tag]:
670
        # Parse PostgreSQL array format
671
        inner = data[1:-1]  # Remove { }
672
        if not inner:
673
            return []
674
        return [load_tag(item.strip()) for item in inner.split(",")]
675
    
676
    conn.adapters.register_dumper(list[Tag], dump_tag_array)
677
    conn.adapters.register_loader(tag_array_info.oid, load_tag_array)
678

679
# Usage
680
register_tag_array(conn)
681

682
tags = [Tag("python", "green"), Tag("database", "blue"), Tag("api", "red")]
683

684
with conn.cursor() as cur:
685
    cur.execute(
686
        "INSERT INTO posts (title, tags) VALUES (%s, %s)",
687
        ("My Post", tags)
688
    )
689
```
690

691
### Dynamic Type Discovery
692

693
```python
694
def discover_custom_types(conn):
695
    """Discover and register all custom types in database"""
696
    
697
    with conn.cursor() as cur:
698
        # Find all custom types
699
        cur.execute("""
700
            SELECT typname, oid, typarray 
701
            FROM pg_type 
702
            WHERE typnamespace = (
703
                SELECT oid FROM pg_namespace WHERE nspname = 'public'
704
            )
705
            AND typtype = 'c'  -- Composite types
706
        """)
707
        
708
        for type_name, oid, array_oid in cur:
709
            print(f"Found custom type: {type_name} (OID: {oid})")
710
            
711
            # Register generic handler for composite types
712
            def load_composite(data: str, type_name=type_name):
713
                # Parse composite type format: (field1,field2,...)
714
                fields = data[1:-1].split(',')  # Remove ( )
715
                return {f"field_{i}": field.strip() for i, field in enumerate(fields)}
716
            
717
            conn.adapters.register_loader(oid, load_composite)
718
            
719
            if array_oid:
720
                # Register array version too
721
                def load_composite_array(data: str, loader=load_composite):
722
                    inner = data[1:-1]  # Remove { }
723
                    if not inner:
724
                        return []
725
                    items = []
726
                    # Parse composite array (more complex parsing needed for real use)
727
                    return [loader(item) for item in inner.split('","')]
728
                
729
                conn.adapters.register_loader(array_oid, load_composite_array)
730

731
# Auto-register all custom types
732
discover_custom_types(conn)
733
```
734

735
### Performance Optimization
736

737
```python
738
# Pre-compile dumpers/loaders for better performance
739
def create_optimized_adapters(conn):
740
    """Create optimized adapters for frequently used types"""
741
    
742
    import json
743
    from decimal import Decimal
744
    
745
    # Fast JSON dumper
746
    json_dumps = json.dumps
747
    def fast_json_dump(obj) -> str:
748
        return json_dumps(obj, separators=(',', ':'))  # No spaces
749
    
750
    # Fast decimal dumper  
751
    def fast_decimal_dump(obj: Decimal) -> str:
752
        return str(obj)
753
    
754
    # Register optimized dumpers
755
    conn.adapters.register_dumper(dict, fast_json_dump)
756
    conn.adapters.register_dumper(list, fast_json_dump)
757
    conn.adapters.register_dumper(Decimal, fast_decimal_dump)
758
```
759

760
## Format Specifications
761

762
```python { .api }
763
from enum import IntEnum
764

765
class PyFormat(IntEnum):
766
    """Query parameter and result format"""
767
    AUTO = 0      # Automatic format selection
768
    TEXT = 1      # Text format  
769
    BINARY = 2    # Binary format
770
```
771

772
## Type System Constants
773

774
```python { .api }
775
# Common PostgreSQL type OIDs
776
OID_BOOL = 16
777
OID_INT2 = 21  
778
OID_INT4 = 23
779
OID_INT8 = 20
780
OID_FLOAT4 = 700
781
OID_FLOAT8 = 701
782
OID_TEXT = 25
783
OID_BYTEA = 17
784
OID_DATE = 1082
785
OID_TIME = 1083
786
OID_TIMESTAMP = 1114
787
OID_TIMESTAMPTZ = 1184
788
OID_INTERVAL = 1186
789
OID_NUMERIC = 1700
790
OID_UUID = 2950
791
OID_JSON = 114
792
OID_JSONB = 3802
793
```