0
# Type System
1

2
Comprehensive PostgreSQL type support including primitive types, arrays, composite types, and custom type conversion with automatic serialization and deserialization.
3

4
## Capabilities
5

6
### PostgreSQL Type Constants
7

8
PostgreSQL type OID constants for all standard data types, enabling type identification and custom type handling.
9

10
```python { .api }
11
# Numeric types
12
BOOLOID: int        # 16 - Boolean
13
INT2OID: int        # 21 - Smallint (2 bytes)
14
INT4OID: int        # 23 - Integer (4 bytes)  
15
INT8OID: int        # 20 - Bigint (8 bytes)
16
FLOAT4OID: int      # 700 - Real (4 bytes)
17
FLOAT8OID: int      # 701 - Double precision (8 bytes)
18
NUMERICOID: int     # 1700 - Numeric/Decimal
19

20
# String types
21
TEXTOID: int        # 25 - Text
22
VARCHAROID: int     # 1043 - Varchar
23
CHAROID: int        # 1042 - Char
24
NAMEOID: int        # 19 - Name
25
BYTEAOID: int       # 17 - Bytea (binary data)
26

27
# Date/time types
28
DATEOID: int        # 1082 - Date
29
TIMEOID: int        # 1083 - Time
30
TIMESTAMPOID: int   # 1114 - Timestamp
31
TIMESTAMPTZOID: int # 1184 - Timestamp with timezone
32
INTERVALOID: int    # 1186 - Interval
33

34
# JSON types
35
JSONOID: int        # 114 - JSON
36
JSONBOID: int       # 3802 - JSONB
37

38
# Other common types
39
UUIDOID: int        # 2950 - UUID
40
XMLOID: int         # 142 - XML
41
INETOID: int        # 869 - inet (IP address)
42
CIDROID: int        # 650 - cidr (IP network)
43
MACADDROID: int     # 829 - macaddr (MAC address)
44

45
# Array type OIDs (add 1000 to base type for array)
46
TEXTARRAYOID: int   # 1009 - Text array
47
INT4ARRAYOID: int   # 1007 - Integer array
48
```
49

50
### Type Mapping Utilities
51

52
Dictionaries for converting between PostgreSQL type OIDs, names, and SQL type names.
53

54
```python { .api }
55
oid_to_name: dict
56
    """
57
    Maps PostgreSQL type OIDs to type names.
58
    
59
    Example: {23: 'int4', 25: 'text', 1700: 'numeric'}
60
    """
61

62
oid_to_sql_name: dict
63
    """
64
    Maps PostgreSQL type OIDs to SQL standard type names.
65
    
66
    Example: {23: 'INTEGER', 25: 'TEXT', 1700: 'NUMERIC'}
67
    """
68

69
name_to_oid: dict
70
    """
71
    Maps PostgreSQL type names to type OIDs.
72
    
73
    Example: {'int4': 23, 'text': 25, 'numeric': 1700}
74
    """
75
```
76

77
### Array Type
78

79
PostgreSQL array type with multi-dimensional support and automatic element type conversion.
80

81
```python { .api }
82
class Array:
83
    """
84
    PostgreSQL array type supporting multi-dimensional arrays with automatic
85
    element type conversion and nested array operations.
86
    """
87
    
88
    def __init__(elements, element_type):
89
        """
90
        Create PostgreSQL array.
91
        
92
        Parameters:
93
        - elements (list): Array elements or nested lists for multi-dimensional
94
        - element_type (int): PostgreSQL type OID for array elements
95
        """
96
    
97
    def __getitem__(index):
98
        """
99
        Get array element by index.
100
        
101
        Parameters:
102
        - index (int or slice): Element index or slice
103
        
104
        Returns:
105
        Element value with automatic type conversion
106
        """
107
    
108
    def __setitem__(index, value):
109
        """
110
        Set array element value.
111
        
112
        Parameters:
113
        - index (int): Element index
114
        - value: New element value
115
        """
116
    
117
    def __len__():
118
        """
119
        Get array length.
120
        
121
        Returns:
122
        int: Number of elements in array
123
        """
124
    
125
    def __iter__():
126
        """Iterate over array elements."""
127
    
128
    def append(value):
129
        """
130
        Append element to array.
131
        
132
        Parameters:
133
        - value: Element to append
134
        """
135
    
136
    def extend(values):
137
        """
138
        Extend array with multiple elements.
139
        
140
        Parameters:
141
        - values (iterable): Elements to add
142
        """
143
    
144
    @property
145
    def dimensions():
146
        """
147
        Get array dimensions.
148
        
149
        Returns:
150
        tuple: Dimension sizes for multi-dimensional arrays
151
        """
152
    
153
    @property
154
    def element_type():
155
        """
156
        Get element type OID.
157
        
158
        Returns:
159
        int: PostgreSQL type OID for array elements
160
        """
161
```
162

163
### Row Type
164

165
Named tuple-like interface for query result rows with both positional and named access.
166

167
```python { .api }
168
class Row:
169
    """
170
    Result row providing named and positional access to column values
171
    with automatic type conversion.
172
    """
173
    
174
    def __getitem__(key):
175
        """
176
        Get column value by index or name.
177
        
178
        Parameters:
179
        - key (int or str): Column index (0-based) or column name
180
        
181
        Returns:
182
        Column value with automatic PostgreSQL type conversion
183
        """
184
    
185
    def __len__():
186
        """
187
        Get number of columns.
188
        
189
        Returns:
190
        int: Number of columns in row
191
        """
192
    
193
    def __iter__():
194
        """Iterate over column values."""
195
    
196
    def keys():
197
        """
198
        Get column names.
199
        
200
        Returns:
201
        list[str]: Column names in order
202
        """
203
    
204
    def values():
205
        """
206
        Get column values.
207
        
208
        Returns:
209
        list: Column values in order
210
        """
211
    
212
    def items():
213
        """
214
        Get column name-value pairs.
215
        
216
        Returns:
217
        list[tuple]: (name, value) pairs
218
        """
219
    
220
    def get(key, default=None):
221
        """
222
        Get column value with default.
223
        
224
        Parameters:
225
        - key (str): Column name
226
        - default: Default value if column not found
227
        
228
        Returns:
229
        Column value or default
230
        """
231
```
232

233
### Type I/O Interface
234

235
Type conversion interface for custom PostgreSQL type handling.
236

237
```python { .api }
238
class TypeIO:
239
    """
240
    Type input/output interface for PostgreSQL type conversion.
241
    """
242
    
243
    def encode(value, format):
244
        """
245
        Encode Python value to PostgreSQL format.
246
        
247
        Parameters:
248
        - value: Python value to encode
249
        - format (str): Output format ('text' or 'binary')
250
        
251
        Returns:
252
        bytes: Encoded value for PostgreSQL
253
        """
254
    
255
    def decode(data, format):
256
        """
257
        Decode PostgreSQL data to Python value.
258
        
259
        Parameters:
260
        - data (bytes): PostgreSQL data
261
        - format (str): Input format ('text' or 'binary')
262
        
263
        Returns:
264
        Python value with appropriate type
265
        """
266
```
267

268
## Usage Examples
269

270
### Working with Arrays
271

272
```python
273
import postgresql
274
import postgresql.types as pg_types
275

276
db = postgresql.open('pq://user:pass@localhost/mydb')
277

278
# Create table with array columns
279
db.execute("""
280
    CREATE TABLE IF NOT EXISTS test_arrays (
281
        id SERIAL PRIMARY KEY,
282
        numbers INTEGER[],
283
        texts TEXT[],
284
        matrix INTEGER[][]
285
    )
286
""")
287

288
# Insert arrays using Python lists
289
insert_stmt = db.prepare("""
290
    INSERT INTO test_arrays (numbers, texts, matrix) 
291
    VALUES ($1, $2, $3)
292
""")
293

294
# PostgreSQL automatically converts Python lists to arrays
295
insert_stmt([1, 2, 3, 4, 5], ['hello', 'world'], [[1, 2], [3, 4]])
296

297
# Query arrays
298
get_arrays = db.prepare("SELECT numbers, texts, matrix FROM test_arrays WHERE id = $1")
299
result = get_arrays.first(1)
300

301
numbers = result['numbers']  # Returns Python list
302
texts = result['texts']      # Returns Python list  
303
matrix = result['matrix']    # Returns nested Python list
304

305
print(f"Numbers: {numbers}")        # [1, 2, 3, 4, 5]
306
print(f"Texts: {texts}")            # ['hello', 'world']
307
print(f"Matrix: {matrix}")          # [[1, 2], [3, 4]]
308

309
# Work with array elements
310
print(f"First number: {numbers[0]}")
311
print(f"Matrix element [1][0]: {matrix[1][0]}")
312
```
313

314
### Type Identification and Conversion
315

316
```python
317
import postgresql
318
import postgresql.types as pg_types
319

320
db = postgresql.open('pq://user:pass@localhost/mydb')
321

322
# Prepare statement and check parameter types
323
stmt = db.prepare("SELECT $1::text, $2::integer, $3::numeric, $4::json")
324

325
print("Parameter types:")
326
for i, type_oid in enumerate(stmt.parameter_types):
327
    type_name = pg_types.oid_to_name.get(type_oid, f"unknown({type_oid})")
328
    sql_name = pg_types.oid_to_sql_name.get(type_oid, f"unknown({type_oid})")
329
    print(f"  ${i+1}: {type_name} (SQL: {sql_name})")
330

331
# Execute with various types
332
result = stmt.first("hello", 42, 123.45, {"key": "value"})
333

334
print("Result types:")
335
for i, type_oid in enumerate(stmt.result_types):
336
    type_name = pg_types.oid_to_name.get(type_oid, f"unknown({type_oid})")
337
    print(f"  Column {i}: {type_name}")
338

339
print(f"Results: {result}")
340
```
341

342
### Custom Type Handling
343

344
```python
345
import postgresql
346
import postgresql.types as pg_types
347
import json
348
from decimal import Decimal
349

350
db = postgresql.open('pq://user:pass@localhost/mydb')
351

352
# Create table with various types
353
db.execute("""
354
    CREATE TABLE IF NOT EXISTS type_examples (
355
        id SERIAL PRIMARY KEY,
356
        json_data JSONB,
357
        money_amount NUMERIC(10,2),
358
        uuid_value UUID,
359
        ip_address INET,
360
        created_at TIMESTAMP WITH TIME ZONE
361
    )
362
""")
363

364
# Insert with automatic type conversion
365
insert_stmt = db.prepare("""
366
    INSERT INTO type_examples (json_data, money_amount, uuid_value, ip_address, created_at)
367
    VALUES ($1, $2, $3, $4, $5)
368
""")
369

370
import uuid
371
from datetime import datetime
372

373
insert_stmt(
374
    {"name": "John", "age": 30, "active": True},  # Dict -> JSONB
375
    Decimal('1234.56'),                           # Decimal -> NUMERIC
376
    uuid.uuid4(),                                 # UUID -> UUID
377
    '192.168.1.1',                               # str -> INET
378
    datetime.now()                               # datetime -> TIMESTAMPTZ
379
)
380

381
# Query with automatic conversion back to Python types
382
select_stmt = db.prepare("SELECT * FROM type_examples WHERE id = $1")
383
result = select_stmt.first(1)
384

385
print(f"JSON data: {result['json_data']}")      # dict
386
print(f"Money: {result['money_amount']}")       # Decimal
387
print(f"UUID: {result['uuid_value']}")          # UUID
388
print(f"IP: {result['ip_address']}")            # str
389
print(f"Created: {result['created_at']}")       # datetime
390
```
391

392
### Working with Composite Types
393

394
```python
395
import postgresql
396

397
db = postgresql.open('pq://user:pass@localhost/mydb')
398

399
# Create composite type
400
db.execute("""
401
    CREATE TYPE IF NOT EXISTS address AS (
402
        street TEXT,
403
        city TEXT,
404
        state TEXT,
405
        zip_code TEXT
406
    )
407
""")
408

409
# Create table using composite type
410
db.execute("""
411
    CREATE TABLE IF NOT EXISTS customers (
412
        id SERIAL PRIMARY KEY,
413
        name TEXT,
414
        address address
415
    )
416
""")
417

418
# Insert using tuple or dict for composite type
419
insert_stmt = db.prepare("""
420
    INSERT INTO customers (name, address) 
421
    VALUES ($1, $2)
422
""")
423

424
# Can use tuple (positional) or dict (named) for composite types
425
insert_stmt("John Doe", ("123 Main St", "Anytown", "CA", "12345"))
426

427
# Query composite type
428
select_stmt = db.prepare("SELECT id, name, address FROM customers WHERE id = $1")
429
result = select_stmt.first(1)
430

431
address = result['address']  # Returns Row object for composite type
432
print(f"Customer: {result['name']}")
433
print(f"Address: {address['street']}, {address['city']}, {address['state']} {address['zip_code']}")
434

435
# Access composite type components
436
print(f"Street: {address['street']}")
437
print(f"City: {address['city']}")
438
```
439

440
### Type Introspection
441

442
```python
443
import postgresql
444
import postgresql.types as pg_types
445

446
db = postgresql.open('pq://user:pass@localhost/mydb')
447

448
# Get information about all types in database
449
type_query = db.prepare("""
450
    SELECT oid, typname, typtype, typlen
451
    FROM pg_type 
452
    WHERE typtype IN ('b', 'c', 'e')  -- base, composite, enum types
453
    ORDER BY typname
454
""")
455

456
print("Database types:")
457
for row in type_query():
458
    type_oid = row['oid']
459
    type_name = row['typname']
460
    type_type = row['typtype']
461
    type_len = row['typlen']
462
    
463
    # Check if we have this type in our mappings
464
    known_name = pg_types.oid_to_name.get(type_oid, "unknown")
465
    sql_name = pg_types.oid_to_sql_name.get(type_oid, "")
466
    
467
    type_category = {
468
        'b': 'base',
469
        'c': 'composite', 
470
        'e': 'enum'
471
    }.get(type_type, 'other')
472
    
473
    print(f"  {type_name} (OID {type_oid}): {type_category}, known as '{known_name}', SQL: '{sql_name}'")
474

475
# Check array types
476
print("\nArray type examples:")
477
array_types = [
478
    (pg_types.TEXTARRAYOID, "text[]"),
479
    (pg_types.INT4ARRAYOID, "integer[]"),
480
    (pg_types.JSONBOID + 1000, "jsonb[]")  # Array OID = base OID + 1000
481
]
482

483
for array_oid, description in array_types:
484
    if array_oid in pg_types.oid_to_name:
485
        print(f"  {description}: OID {array_oid}")
486
```