0
# Type Adaptation and PostgreSQL Types
1

2
Comprehensive type system for converting between Python objects and PostgreSQL data types. psycopg2 provides automatic adaptation for common types and extensible mechanisms for custom types including arrays, JSON/JSONB, ranges, IP addresses, and user-defined types.
3

4
## Capabilities
5

6
### Core Type Adaptation
7

8
Fundamental type adaptation system allowing registration of adapters and type casters for bidirectional conversion between Python and PostgreSQL types.
9

10
```python { .api }
11
def adapt(obj):
12
    """
13
    Adapt Python object for PostgreSQL.
14
    
15
    Parameters:
16
    - obj: Python object to adapt
17
    
18
    Returns:
19
    ISQLQuote: Adapted object ready for SQL inclusion
20
    """
21

22
def register_adapter(typ, callable):
23
    """
24
    Register adapter for Python type.
25
    
26
    Parameters:
27
    - typ (type): Python type to adapt
28
    - callable: Adapter function or class
29
    """
30

31
def new_type(oids, name, castfunc):
32
    """
33
    Create new type caster for PostgreSQL type.
34
    
35
    Parameters:
36
    - oids (tuple): PostgreSQL type OIDs
37
    - name (str): Type name
38
    - castfunc (callable): Cast function
39
    
40
    Returns:
41
    Type caster object
42
    """
43

44
def register_type(obj, scope=None):
45
    """
46
    Register type caster.
47
    
48
    Parameters:
49
    - obj: Type caster object
50
    - scope (connection/cursor, optional): Registration scope
51
    """
52

53
def new_array_type(oids, name, base_caster):
54
    """
55
    Create array type caster.
56
    
57
    Parameters:
58
    - oids (tuple): Array type OIDs
59
    - name (str): Array type name  
60
    - base_caster: Base element type caster
61
    
62
    Returns:
63
    Array type caster
64
    """
65
```
66

67
### JSON and JSONB Support
68

69
Native support for PostgreSQL JSON and JSONB data types with automatic serialization/deserialization and custom encoder support.
70

71
```python { .api }
72
class Json:
73
    """JSON adapter for PostgreSQL json/jsonb types."""
74
    
75
    def __init__(self, adapted, dumps=None):
76
        """
77
        Initialize JSON adapter.
78
        
79
        Parameters:
80
        - adapted: Python object to adapt
81
        - dumps (callable, optional): Custom JSON encoder
82
        """
83
    
84
    def dumps(self, obj):
85
        """
86
        Serialize object to JSON.
87
        
88
        Parameters:
89
        - obj: Object to serialize
90
        
91
        Returns:
92
        str: JSON string
93
        """
94

95
def register_json(conn_or_curs=None, globally=False, loads=None, oid=None, array_oid=None, name='json'):
96
    """
97
    Register JSON type casters.
98
    
99
    Parameters:
100
    - conn_or_curs (connection/cursor, optional): Registration scope
101
    - globally (bool): Register globally
102
    - loads (callable, optional): Custom JSON decoder
103
    - oid (int, optional): JSON type OID
104
    - array_oid (int, optional): JSON array type OID
105
    - name (str): Type name to register
106
    
107
    Returns:
108
    tuple: (JSON_caster, JSONARRAY_caster)
109
    """
110

111
def register_default_json(conn_or_curs=None, globally=False, loads=None):
112
    """
113
    Register default JSON type casters for PostgreSQL 9.2+.
114
    
115
    Parameters:
116
    - conn_or_curs (connection/cursor, optional): Registration scope
117
    - globally (bool): Register globally
118
    - loads (callable, optional): Custom JSON decoder
119
    
120
    Returns:
121
    tuple: (JSON_caster, JSONARRAY_caster)
122
    """
123

124
def register_default_jsonb(conn_or_curs=None, globally=False, loads=None):
125
    """
126
    Register default JSONB type casters for PostgreSQL 9.4+.
127
    
128
    Parameters:
129
    - conn_or_curs (connection/cursor, optional): Registration scope  
130
    - globally (bool): Register globally
131
    - loads (callable, optional): Custom JSON decoder
132
    
133
    Returns:
134
    tuple: (JSONB_caster, JSONBARRAY_caster)
135
    """
136
```
137

138
Usage examples:
139

140
```python
141
# Basic JSON usage
142
from psycopg2.extras import Json
143
data = {'name': 'Alice', 'age': 30}
144
cur.execute("INSERT INTO users (data) VALUES (%s)", (Json(data),))
145

146
# Retrieve JSON data
147
cur.execute("SELECT data FROM users WHERE id = %s", (1,))
148
user_data = cur.fetchone()[0]  # Automatically decoded to Python dict
149

150
# Custom JSON encoder/decoder
151
import json
152
def custom_encoder(obj):
153
    return json.dumps(obj, indent=2)
154

155
def custom_decoder(s):
156
    return json.loads(s)
157

158
# Register with custom functions
159
register_default_json(loads=custom_decoder)
160
cur.execute("INSERT INTO users (data) VALUES (%s)", (Json(data, dumps=custom_encoder),))
161
```
162

163
### Range Types
164

165
Support for PostgreSQL range types including numeric ranges, date ranges, and timestamp ranges.
166

167
```python { .api }
168
class Range:
169
    """Base class for PostgreSQL range types."""
170
    
171
    def __init__(self, lower=None, upper=None, bounds='[)', empty=False):
172
        """
173
        Initialize range.
174
        
175
        Parameters:
176
        - lower: Lower bound value
177
        - upper: Upper bound value  
178
        - bounds (str): Bound inclusion flags ('[)', '(]', '()', '[]')
179
        - empty (bool): Create empty range
180
        """
181
    
182
    @property
183
    def lower(self):
184
        """Lower bound value."""
185
    
186
    @property  
187
    def upper(self):
188
        """Upper bound value."""
189
        
190
    @property
191
    def isempty(self):
192
        """True if range is empty."""
193
        
194
    @property
195
    def lower_inf(self):
196
        """True if lower bound is infinite."""
197
        
198
    @property
199
    def upper_inf(self):
200
        """True if upper bound is infinite."""
201
        
202
    @property
203
    def lower_inc(self):
204
        """True if lower bound is included."""
205
        
206
    @property
207
    def upper_inc(self):
208
        """True if upper bound is included."""
209
    
210
    def __contains__(self, x):
211
        """Check if value is in range."""
212

213
class NumericRange(Range):
214
    """Range for numeric types (int4range, int8range, numrange)."""
215

216
class DateRange(Range):
217
    """Range for date type (daterange)."""
218

219
class DateTimeRange(Range):
220
    """Range for timestamp type (tsrange)."""
221

222
class DateTimeTZRange(Range):
223
    """Range for timestamp with timezone type (tstzrange)."""
224

225
def register_range(pgrange, pyrange, conn_or_curs=None, globally=False):
226
    """
227
    Register range type adapter and caster.
228
    
229
    Parameters:
230
    - pgrange (str): PostgreSQL range type name
231
    - pyrange (type): Python range class
232
    - conn_or_curs (connection/cursor, optional): Registration scope
233
    - globally (bool): Register globally
234
    """
235
    
236
class RangeAdapter:
237
    """Adapter for range types."""
238
    
239
    def __init__(self, wrapped):
240
        """Initialize range adapter."""
241

242
class RangeCaster:
243
    """Type caster for range types."""
244
    
245
    def __init__(self, pgrange, pyrange, parse):
246
        """Initialize range caster."""
247
```
248

249
Usage examples:
250

251
```python
252
from psycopg2.extras import NumericRange, DateRange
253
from datetime import date
254

255
# Numeric ranges
256
num_range = NumericRange(1, 10)  # [1,10)
257
num_range_inclusive = NumericRange(1, 10, '[]')  # [1,10]
258
empty_range = NumericRange(empty=True)
259

260
# Date ranges  
261
date_range = DateRange(date(2023, 1, 1), date(2023, 12, 31))
262

263
# Check containment
264
if 5 in num_range:
265
    print("5 is in range")
266

267
# Store ranges
268
cur.execute("INSERT INTO events (date_range) VALUES (%s)", (date_range,))
269

270
# Retrieve ranges
271
cur.execute("SELECT date_range FROM events WHERE id = %s", (1,))
272
retrieved_range = cur.fetchone()[0]
273
print(f"Range: {retrieved_range.lower} to {retrieved_range.upper}")
274
```
275

276
### IP Address Support
277

278
Support for PostgreSQL inet and cidr types using Python's ipaddress module.
279

280
```python { .api }
281
def register_ipaddress(conn_or_curs=None):
282
    """
283
    Register IP address type support for PostgreSQL network types.
284
    
285
    Parameters:
286
    - conn_or_curs (connection/cursor, optional): Registration scope. If None, register globally.
287
    
288
    Note:
289
    - inet values convert to IPv4Interface/IPv6Interface objects
290
    - cidr values convert to IPv4Network/IPv6Network objects
291
    """
292

293
def cast_interface(s, cur=None):
294
    """Cast PostgreSQL inet value to ipaddress interface object."""
295

296
def cast_network(s, cur=None):
297
    """Cast PostgreSQL cidr value to ipaddress network object."""
298

299
def adapt_ipaddress(obj):
300
    """Adapt ipaddress objects for PostgreSQL."""
301
```
302

303
Usage examples:
304

305
```python
306
from psycopg2._ipaddress import register_ipaddress
307
import ipaddress
308

309
# Register IP address support
310
register_ipaddress()
311

312
# Use Python ipaddress objects
313
interface = ipaddress.ip_interface('192.168.1.1/24')  # inet type
314
network = ipaddress.ip_network('192.168.1.0/24')     # cidr type
315

316
cur.execute("INSERT INTO hosts (interface, network) VALUES (%s, %s)", (interface, network))
317

318
# Retrieve as ipaddress objects
319
cur.execute("SELECT interface, network FROM hosts WHERE id = %s", (1,)) 
320
retrieved_interface, retrieved_network = cur.fetchone()
321
print(f"Interface: {retrieved_interface}")  # IPv4Interface object
322
print(f"Network: {retrieved_network}")      # IPv4Network object
323

324
# Works with both IPv4 and IPv6
325
ipv6_interface = ipaddress.ip_interface('::1/128')
326
cur.execute("INSERT INTO hosts (interface) VALUES (%s)", (ipv6_interface,))
327
```
328

329
### Array Types
330

331
Automatic support for PostgreSQL array types with bidirectional conversion to Python lists.
332

333
```python
334
# Arrays are automatically handled
335
int_array = [1, 2, 3, 4, 5]
336
text_array = ['hello', 'world']
337

338
cur.execute("INSERT INTO data (integers, texts) VALUES (%s, %s)", 
339
           (int_array, text_array))
340

341
# Retrieve arrays
342
cur.execute("SELECT integers, texts FROM data WHERE id = %s", (1,))
343
ints, texts = cur.fetchone()
344
print(f"Integers: {ints}, Texts: {texts}")  # Python lists
345

346
# Multidimensional arrays
347
matrix = [[1, 2], [3, 4]]
348
cur.execute("INSERT INTO matrices (data) VALUES (%s)", (matrix,))
349
```
350

351
### UUID Support
352

353
Support for PostgreSQL UUID type using Python's uuid module.
354

355
```python { .api }
356
def register_uuid(oids=None, conn_or_curs=None, globally=False):
357
    """
358
    Register UUID type support.
359
    
360
    Parameters:
361
    - oids (tuple, optional): UUID type OIDs
362
    - conn_or_curs (connection/cursor, optional): Registration scope
363
    - globally (bool): Register globally
364
    """
365
```
366

367
Usage examples:
368

369
```python
370
from psycopg2.extras import register_uuid
371
import uuid
372

373
# Register UUID support
374
register_uuid()
375

376
# Use Python UUID objects
377
user_id = uuid.uuid4()
378
cur.execute("INSERT INTO users (id, name) VALUES (%s, %s)", (user_id, 'Alice'))
379

380
# Retrieve as UUID objects
381
cur.execute("SELECT id FROM users WHERE name = %s", ('Alice',))
382
retrieved_id = cur.fetchone()[0]
383
print(f"User ID: {retrieved_id}")  # UUID object
384
```
385

386
### Timezone Support
387

388
Timezone utilities for working with PostgreSQL timestamp types and time zone conversions.
389

390
```python { .api }
391
class FixedOffsetTimezone(datetime.tzinfo):
392
    """
393
    Fixed offset timezone implementation.
394
    
395
    Parameters:
396
    - offset (timedelta/int, optional): UTC offset as timedelta or minutes
397
    - name (str, optional): Timezone name
398
    """
399
    
400
    def __init__(self, offset=None, name=None): ...
401
    def utcoffset(self, dt): ...
402
    def tzname(self, dt): ...
403
    def dst(self, dt): ...
404

405
class LocalTimezone(datetime.tzinfo):
406
    """Platform's local timezone implementation."""
407
    
408
    def utcoffset(self, dt): ...
409
    def dst(self, dt): ...
410
    def tzname(self, dt): ...
411

412
# Constants
413
LOCAL: LocalTimezone  # Local timezone instance
414
ZERO: datetime.timedelta  # Zero timedelta constant
415
```
416

417
Usage examples:
418

419
```python
420
from psycopg2.tz import FixedOffsetTimezone, LocalTimezone, LOCAL
421
import datetime
422

423
# Create fixed offset timezone (UTC+5:30)
424
ist = FixedOffsetTimezone(offset=330)  # 330 minutes
425
# or using timedelta
426
ist = FixedOffsetTimezone(offset=datetime.timedelta(hours=5, minutes=30))
427

428
# Create datetime with timezone
429
dt = datetime.datetime(2023, 1, 1, 12, 0, 0, tzinfo=ist)
430

431
# Use local timezone
432
local_dt = datetime.datetime(2023, 1, 1, 12, 0, 0, tzinfo=LOCAL)
433

434
# Store timezone-aware datetime
435
cur.execute("INSERT INTO events (event_time) VALUES (%s)", (dt,))
436

437
# Retrieve with timezone information preserved
438
cur.execute("SELECT event_time FROM events")
439
retrieved_dt = cur.fetchone()[0]
440
print(f"Event time: {retrieved_dt}")  # Includes timezone info
441
```
442

443
### HStore Support
444

445
Support for PostgreSQL hstore extension providing key-value storage.
446

447
```python { .api }
448
def register_hstore(conn_or_curs, globally=False, unicode=False, oid=None, array_oid=None):
449
    """
450
    Register hstore type support.
451
    
452
    Parameters:
453
    - conn_or_curs (connection/cursor): Connection to query hstore OIDs
454
    - globally (bool): Register globally
455
    - unicode (bool): Return unicode strings
456
    - oid (int, optional): hstore type OID
457
    - array_oid (int, optional): hstore array type OID
458
    """
459

460
class HstoreAdapter:
461
    """Adapter for hstore type."""
462
    
463
    def __init__(self, wrapped):
464
        """Initialize hstore adapter."""
465
```
466

467
Usage examples:
468

469
```python
470
from psycopg2.extras import register_hstore
471

472
# Register hstore (requires hstore extension in database)
473
register_hstore(conn)
474

475
# Use Python dictionaries
476
data = {'key1': 'value1', 'key2': 'value2'}
477
cur.execute("INSERT INTO products (attributes) VALUES (%s)", (data,))
478

479
# Retrieve as dictionaries
480
cur.execute("SELECT attributes FROM products WHERE id = %s", (1,))
481
attrs = cur.fetchone()[0]
482
print(f"Attributes: {attrs}")  # Python dict
483
```
484

485
### Composite Types
486

487
Support for PostgreSQL composite types (user-defined row types).
488

489
```python { .api }
490
def register_composite(name, conn_or_curs, globally=False, factory=None):
491
    """
492
    Register composite type support.
493
    
494
    Parameters:
495
    - name (str): Composite type name
496
    - conn_or_curs (connection/cursor): Connection to query type info
497
    - globally (bool): Register globally
498
    - factory (callable, optional): Factory function for composite objects
499
    
500
    Returns:
501
    Type caster for composite type
502
    """
503

504
class CompositeCaster:
505
    """Type caster for composite types."""
506
    
507
    def __init__(self, name, oid, attrs, array_oid=None, schema=None):
508
        """Initialize composite caster."""
509
```
510

511
Usage examples:
512

513
```python
514
from psycopg2.extras import register_composite
515
from collections import namedtuple
516

517
# Create composite type in database
518
cur.execute("""
519
    CREATE TYPE person AS (
520
        name text,
521
        age integer,
522
        email text
523
    )
524
""")
525

526
# Register composite type
527
Person = register_composite('person', cur)
528

529
# Use composite type
530
person = Person('Alice', 30, 'alice@example.com')
531
cur.execute("INSERT INTO people (data) VALUES (%s)", (person,))
532

533
# Retrieve composite objects
534
cur.execute("SELECT data FROM people WHERE id = %s", (1,))
535
retrieved_person = cur.fetchone()[0]
536
print(f"Name: {retrieved_person.name}, Age: {retrieved_person.age}")
537
```
538

539
## Types
540

541
### Adapter Interface
542

543
```python { .api }
544
class ISQLQuote:
545
    """Interface for SQL quotable objects."""
546
    
547
    def getquoted(self):
548
        """Return quoted representation as bytes."""
549
    
550
    def prepare(self, conn):
551
        """Prepare adapter for specific connection."""
552
```
553

554
### Built-in Adapters
555

556
```python { .api }
557
class AsIs:
558
    """Pass-through adapter (no quoting)."""
559
    
560
class Binary:
561
    """Binary data adapter."""
562
    
563
class Boolean:
564
    """Boolean adapter."""
565
    
566
class Float:
567
    """Float adapter."""
568
    
569
class Int:
570
    """Integer adapter."""
571
    
572
class QuotedString:
573
    """String adapter with proper quoting."""
574
```
575

576
### Type Constants
577

578
```python { .api }
579
# PostgreSQL type OIDs
580
BOOLEAN: int = 16
581
BYTEA: int = 17
582
INT8: int = 20
583
INT2: int = 21
584
INT4: int = 23
585
TEXT: int = 25
586
FLOAT4: int = 700
587
FLOAT8: int = 701
588
TIMESTAMP: int = 1114
589
TIMESTAMPTZ: int = 1184
590
DATE: int = 1082
591
TIME: int = 1083
592

593
# Array type OIDs  
594
BOOLEANARRAY: int = 1000
595
INTEGERARRAY: int = 1007
596
FLOATARRAY: int = 1021
597
STRINGARRAY: int = 1009
598

599
# JSON type OIDs
600
JSON_OID: int = 114
601
JSONARRAY_OID: int = 199
602
JSONB_OID: int = 3802
603
JSONBARRAY_OID: int = 3807
604
```