0
# Type System
1

2
Comprehensive type system supporting all PostgreSQL data types, custom type registration, automatic encoding/decoding, geometric types, arrays, composite types, and performance-optimized codecs.
3

4
## Capabilities
5

6
### Built-in Type Support
7

8
AsyncPG automatically handles all standard PostgreSQL data types with optimized codecs.
9

10
#### Numeric Types
11
- `smallint`, `integer`, `bigint` → Python `int`
12
- `decimal`, `numeric` → Python `decimal.Decimal`
13
- `real`, `double precision` → Python `float`
14
- `money` → Python `decimal.Decimal`
15

16
#### String Types
17
- `char`, `varchar`, `text` → Python `str`
18
- `bytea` → Python `bytes`
19

20
#### Date/Time Types
21
- `date` → Python `datetime.date`
22
- `time`, `timetz` → Python `datetime.time`
23
- `timestamp`, `timestamptz` → Python `datetime.datetime`
24
- `interval` → Python `datetime.timedelta`
25

26
#### Boolean and UUID
27
- `boolean` → Python `bool`
28
- `uuid` → Python `uuid.UUID`
29

30
#### JSON Types
31
- `json`, `jsonb` → Python `dict`, `list`, or JSON-serializable types
32

33
#### Network Types
34
- `inet` → Python `ipaddress.IPv4Address` or `ipaddress.IPv6Address`
35
- `cidr` → Python `ipaddress.IPv4Network` or `ipaddress.IPv6Network`
36
- `macaddr` → Python `str`
37

38
### Array Type Support
39

40
Automatic handling of PostgreSQL arrays with full nesting support.
41

42
```python
43
# Insert arrays
44
await conn.execute(
45
    "INSERT INTO data(tags, scores) VALUES($1, $2)",
46
    ["python", "database", "async"],  # text[]
47
    [95, 87, 92]                     # integer[]
48
)
49

50
# Fetch arrays
51
row = await conn.fetchrow("SELECT tags, scores FROM data WHERE id = 1")
52
print(row['tags'])    # ['python', 'database', 'async']
53
print(row['scores'])  # [95, 87, 92]
54

55
# Multi-dimensional arrays
56
matrix = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
57
await conn.execute("INSERT INTO matrices(data) VALUES($1)", matrix)
58

59
# Array operations in queries
60
users = await conn.fetch(
61
    "SELECT * FROM users WHERE $1 = ANY(roles)",
62
    "admin"
63
)
64
```
65

66
### Geometric Types
67

68
Support for PostgreSQL's geometric data types with specialized Python classes.
69

70
```python { .api }
71
# Geometric types from asyncpg.types
72
class Point:
73
    """PostgreSQL point type (x, y)."""
74
    x: float
75
    y: float
76
    
77
    def __init__(self, x: float, y: float): ...
78

79
class Box:
80
    """PostgreSQL box type (rectangular area)."""
81
    high: Point
82
    low: Point
83
    
84
    def __init__(self, high: Point, low: Point): ...
85

86
class Path:
87
    """PostgreSQL path type (series of connected points)."""
88
    is_closed: bool
89
    points: typing.List[Point]
90
    
91
    def __init__(self, points: typing.List[Point], is_closed: bool = False): ...
92

93
class Polygon:
94
    """PostgreSQL polygon type (closed path)."""
95
    points: typing.List[Point]
96
    
97
    def __init__(self, points: typing.List[Point]): ...
98

99
class Line:
100
    """PostgreSQL line type (infinite line)."""
101
    a: float
102
    b: float  
103
    c: float
104
    
105
    def __init__(self, a: float, b: float, c: float): ...
106

107
class LineSegment:
108
    """PostgreSQL line segment type (finite line)."""
109
    p1: Point
110
    p2: Point
111
    
112
    def __init__(self, p1: Point, p2: Point): ...
113

114
class Circle:
115
    """PostgreSQL circle type (center point and radius)."""
116
    center: Point
117
    radius: float
118
    
119
    def __init__(self, center: Point, radius: float): ...
120

121
class BitString:
122
    """PostgreSQL bit string type."""
123
    value: str
124
    
125
    def __init__(self, value: str): ...
126
```
127

128
#### Example Usage
129

130
```python
131
from asyncpg.types import Point, Box, Circle, Polygon
132

133
# Insert geometric data
134
location = Point(40.7128, -74.0060)  # New York City
135
await conn.execute(
136
    "INSERT INTO locations(name, coordinates) VALUES($1, $2)",
137
    "NYC", location
138
)
139

140
# Create geometric objects
141
area = Box(Point(0, 0), Point(10, 10))
142
region = Circle(Point(5, 5), 3.0)
143
shape = Polygon([Point(0, 0), Point(5, 0), Point(5, 5), Point(0, 5)])
144

145
await conn.execute(
146
    "INSERT INTO regions(area, circle, shape) VALUES($1, $2, $3)",
147
    area, region, shape
148
)
149

150
# Query with geometric operations
151
nearby = await conn.fetch(
152
    "SELECT * FROM locations WHERE coordinates <-> $1 < $2",
153
    Point(40.7580, -73.9855),  # Times Square
154
    2.0  # Within 2 units
155
)
156
```
157

158
### Range Types
159

160
Support for PostgreSQL range types with comprehensive range operations.
161

162
```python { .api }
163
class Range:
164
    """PostgreSQL range type representation."""
165
    
166
    def __init__(
167
        self,
168
        lower: typing.Any = None,
169
        upper: typing.Any = None,
170
        *,
171
        lower_inc: bool = True,
172
        upper_inc: bool = False,
173
        empty: bool = False
174
    ) -> None: ...
175
    
176
    @property
177
    def lower(self) -> typing.Any: ...
178
    
179
    @property
180
    def upper(self) -> typing.Any: ...
181
    
182
    @property
183
    def lower_inc(self) -> bool: ...
184
    
185
    @property
186
    def upper_inc(self) -> bool: ...
187
    
188
    @property
189
    def lower_inf(self) -> bool: ...
190
    
191
    @property
192
    def upper_inf(self) -> bool: ...
193
    
194
    @property
195
    def isempty(self) -> bool: ...
196
    
197
    def issubset(self, other: Range) -> bool: ...
198
    
199
    def issuperset(self, other: Range) -> bool: ...
200
```
201

202
#### Example Usage
203

204
```python
205
from asyncpg.types import Range
206
from datetime import datetime, date
207

208
# Date ranges
209
availability = Range(
210
    date(2024, 1, 1),
211
    date(2024, 12, 31),
212
    upper_inc=True
213
)
214

215
# Time ranges  
216
business_hours = Range(
217
    datetime(2024, 1, 1, 9, 0),
218
    datetime(2024, 1, 1, 17, 0)
219
)
220

221
# Numeric ranges
222
price_range = Range(100, 500, lower_inc=True, upper_inc=False)
223

224
await conn.execute(
225
    "INSERT INTO products(name, price_range, availability) VALUES($1, $2, $3)",
226
    "Widget", price_range, availability
227
)
228

229
# Range queries
230
products = await conn.fetch(
231
    "SELECT * FROM products WHERE price_range @> $1",
232
    250  # Contains this value
233
)
234

235
overlapping = await conn.fetch(
236
    "SELECT * FROM events WHERE time_range && $1",
237
    business_hours  # Overlaps with business hours
238
)
239
```
240

241
### Custom Type Codecs
242

243
Register custom encoders and decoders for specialized data types.
244

245
```python { .api }
246
async def set_type_codec(
247
    self,
248
    typename: str,
249
    *,
250
    schema: str = 'public',
251
    encoder: typing.Callable,
252
    decoder: typing.Callable,
253
    format: str = 'text'
254
) -> None:
255
    """
256
    Set an encoder/decoder pair for the specified data type.
257
    
258
    Parameters:
259
    typename: PostgreSQL type name
260
    schema: Schema containing the type
261
    encoder: Function to encode Python values to PostgreSQL format
262
    decoder: Function to decode PostgreSQL values to Python objects
263
    format: Codec format ('text' or 'binary')
264
    """
265

266
async def reset_type_codec(
267
    self,
268
    typename: str,
269
    *,
270
    schema: str = 'public'
271
) -> None:
272
    """
273
    Reset typename codec to the default implementation.
274
    
275
    Parameters:
276
    typename: PostgreSQL type name
277
    schema: Schema containing the type
278
    """
279

280
async def set_builtin_type_codec(
281
    self,
282
    typename: str,
283
    *,
284
    schema: str = 'public',
285
    codec_name: str,
286
    format: str = None
287
) -> None:
288
    """
289
    Set a builtin codec for the specified scalar data type.
290
    
291
    Parameters:
292
    typename: PostgreSQL type name
293
    schema: Schema containing the type
294
    codec_name: Name of builtin codec to use
295
    format: Codec format ('text' or 'binary')
296
    """
297
```
298

299
#### Example Usage
300

301
```python
302
import json
303
from datetime import datetime
304
from decimal import Decimal
305

306
# JSON codec for custom JSON handling
307
def encode_json(value):
308
    return json.dumps(value, ensure_ascii=False)
309

310
def decode_json(value):
311
    return json.loads(value)
312

313
await conn.set_type_codec(
314
    'json',
315
    encoder=encode_json,
316
    decoder=decode_json
317
)
318

319
# Custom enum codec
320
def encode_status(value):
321
    return value.value if hasattr(value, 'value') else str(value)
322

323
def decode_status(value):
324
    from enum import Enum
325
    class Status(Enum):
326
        PENDING = 'pending'
327
        ACTIVE = 'active'
328
        INACTIVE = 'inactive'
329
    return Status(value)
330

331
await conn.set_type_codec(
332
    'user_status',
333
    encoder=encode_status,
334
    decoder=decode_status
335
)
336

337
# Money type with custom precision
338
def encode_money(value):
339
    """Encode Decimal to money format."""
340
    return str(value)
341

342
def decode_money(value):
343
    """Decode money to Decimal with proper precision."""
344
    # Remove currency symbol and convert
345
    cleaned = value.replace('$', '').replace(',', '')
346
    return Decimal(cleaned)
347

348
await conn.set_type_codec(
349
    'money',
350
    encoder=encode_money,
351
    decoder=decode_money
352
)
353
```
354

355
### Composite Type Support
356

357
Automatic handling of PostgreSQL composite types (user-defined record types).
358

359
```python
360
# Create composite type in PostgreSQL
361
await conn.execute("""
362
    CREATE TYPE address AS (
363
        street text,
364
        city text,
365
        state text,
366
        zip_code text
367
    )
368
""")
369

370
# Python representation
371
class Address:
372
    def __init__(self, street, city, state, zip_code):
373
        self.street = street
374
        self.city = city
375
        self.state = state
376
        self.zip_code = zip_code
377

378
# Register composite type codec
379
def encode_address(addr):
380
    return (addr.street, addr.city, addr.state, addr.zip_code)
381

382
def decode_address(row):
383
    return Address(*row)
384

385
await conn.set_type_codec(
386
    'address',
387
    encoder=encode_address,
388
    decoder=decode_address
389
)
390

391
# Use composite type
392
home_address = Address("123 Main St", "Anytown", "NY", "12345")
393
await conn.execute(
394
    "INSERT INTO users(name, address) VALUES($1, $2)",
395
    "John Doe", home_address
396
)
397

398
user = await conn.fetchrow("SELECT name, address FROM users WHERE id = 1")
399
print(f"User: {user['name']}")
400
print(f"Address: {user['address'].street}, {user['address'].city}")
401
```
402

403
### Type Introspection
404

405
Access PostgreSQL type information for dynamic type handling.
406

407
```python { .api }
408
class Type:
409
    """Database data type information."""
410
    oid: int        # PostgreSQL type OID
411
    name: str       # Type name (e.g., 'int4', 'text')
412
    kind: str       # Type kind ('scalar', 'array', 'composite', 'range')
413
    schema: str     # Schema name
414

415
class Attribute:
416
    """Database relation attribute."""
417
    name: str       # Attribute name
418
    type: Type      # Attribute data type
419
```
420

421
#### Example Usage
422

423
```python
424
# Get type information
425
type_info = await conn.fetchrow(
426
    "SELECT * FROM pg_type WHERE typname = $1",
427
    'json'
428
)
429

430
# Introspect table structure
431
columns = await conn.fetch("""
432
    SELECT column_name, data_type, is_nullable
433
    FROM information_schema.columns 
434
    WHERE table_name = $1
435
    ORDER BY ordinal_position
436
""", 'users')
437

438
# Dynamic type handling based on introspection
439
for col in columns:
440
    print(f"Column: {col['column_name']}, Type: {col['data_type']}")
441
    
442
    # Set appropriate codec based on type
443
    if col['data_type'] == 'jsonb':
444
        await conn.set_type_codec(
445
            'jsonb',
446
            encoder=json.dumps,
447
            decoder=json.loads
448
        )
449
```
450

451
### Schema State Management
452

453
Manage type system state and handle schema changes.
454

455
```python { .api }
456
async def reload_schema_state(self) -> None:
457
    """
458
    Indicate that database schema information must be reloaded.
459
    
460
    Call this after schema changes that affect type definitions,
461
    such as creating new types, modifying composite types, or
462
    changing type codecs.
463
    """
464
```
465

466
#### Example Usage
467

468
```python
469
# After schema changes, reload type information
470
await conn.execute("CREATE TYPE new_enum AS ENUM ('a', 'b', 'c')")
471
await conn.reload_schema_state()
472

473
# Register codec for new type
474
def encode_enum(value):
475
    return value.value if hasattr(value, 'value') else str(value)
476

477
def decode_enum(value):
478
    from enum import Enum
479
    class NewEnum(Enum):
480
        A = 'a'
481
        B = 'b' 
482
        C = 'c'
483
    return NewEnum(value)
484

485
await conn.set_type_codec('new_enum', encoder=encode_enum, decoder=decode_enum)
486
```
487

488
### Performance Considerations
489

490
Optimize type handling for maximum performance.
491

492
```python
493
# Use binary format for better performance with large datasets
494
await conn.set_type_codec(
495
    'large_json_type',
496
    encoder=json.dumps,
497
    decoder=json.loads,
498
    format='binary'  # Faster for large values
499
)
500

501
# Pre-register commonly used codecs at connection setup
502
async def setup_connection(conn):
503
    """Setup function called for each new connection."""
504
    
505
    # JSON codec
506
    await conn.set_type_codec(
507
        'jsonb',
508
        encoder=json.dumps,
509
        decoder=json.loads
510
    )
511
    
512
    # Custom enum types
513
    await conn.set_type_codec('status_enum', encoder=str, decoder=StatusEnum)
514
    
515
    # UUID codec optimization
516
    await conn.set_builtin_type_codec('uuid', codec_name='uuid')
517

518
# Use setup function with connection pool
519
pool = asyncpg.create_pool(dsn, setup=setup_connection)
520
```
521

522
## Types
523

524
```python { .api }
525
# Core type system classes
526
class Type:
527
    """Database data type information."""
528
    oid: int
529
    name: str
530
    kind: str
531
    schema: str
532

533
class Attribute:
534
    """Database relation attribute."""
535
    name: str
536
    type: Type
537

538
class ServerVersion:
539
    """PostgreSQL server version tuple."""
540
    major: int
541
    minor: int
542
    micro: int
543
    releaselevel: str
544
    serial: int
545

546
# Geometric types
547
class Point:
548
    x: float
549
    y: float
550
    def __init__(self, x: float, y: float): ...
551

552
class Box:
553
    high: Point
554
    low: Point
555
    def __init__(self, high: Point, low: Point): ...
556

557
class Path:
558
    is_closed: bool
559
    points: typing.List[Point]
560
    def __init__(self, points: typing.List[Point], is_closed: bool = False): ...
561

562
class Polygon:
563
    points: typing.List[Point]
564
    def __init__(self, points: typing.List[Point]): ...
565

566
class Line:
567
    a: float
568
    b: float
569
    c: float
570
    def __init__(self, a: float, b: float, c: float): ...
571

572
class LineSegment:
573
    p1: Point
574
    p2: Point
575
    def __init__(self, p1: Point, p2: Point): ...
576

577
class Circle:
578
    center: Point
579
    radius: float
580
    def __init__(self, center: Point, radius: float): ...
581

582
class BitString:
583
    value: str
584
    def __init__(self, value: str): ...
585

586
# Range types
587
class Range:
588
    lower: typing.Any
589
    upper: typing.Any
590
    lower_inc: bool
591
    upper_inc: bool
592
    lower_inf: bool
593
    upper_inf: bool
594
    isempty: bool
595
    
596
    def __init__(self, lower: typing.Any = None, upper: typing.Any = None, *, lower_inc: bool = True, upper_inc: bool = False, empty: bool = False): ...
597
    def issubset(self, other: Range) -> bool: ...
598
    def issuperset(self, other: Range) -> bool: ...
599

600
# Type codec signatures
601
Encoder = typing.Callable[[typing.Any], typing.Union[str, bytes]]
602
Decoder = typing.Callable[[typing.Union[str, bytes]], typing.Any]
603
```