0
# PostgreSQL Data Types
1

2
Comprehensive support for all PostgreSQL data types with automatic Python conversion, including numeric types, text types, date/time types, arrays, JSON, network types, and PostgreSQL-specific types.
3

4
## Capabilities
5

6
### Numeric Type Constants
7

8
PostgreSQL numeric data type identifiers with their corresponding OID values.
9

10
```python { .api }
11
# Integer types
12
BIGINT: int = 20
13
"""64-bit signed integer type."""
14

15
INTEGER: int = 23  
16
"""32-bit signed integer type."""
17

18
SMALLINT: int = 21
19
"""16-bit signed integer type."""
20

21
# Floating point types
22
NUMERIC: int = 1700
23
"""Arbitrary precision decimal type."""
24

25
FLOAT: int = 701
26
"""Double precision floating point type."""
27

28
REAL: int = 700
29
"""Single precision floating point type."""
30

31
# Money type
32
MONEY: int = 790
33
"""Currency amount type."""
34

35
MONEY_ARRAY: int = 791
36
"""Array of money values."""
37
```
38

39
### Text and String Type Constants  
40

41
PostgreSQL text and character data type identifiers.
42

43
```python { .api }
44
TEXT: int = 25
45
"""Variable-length text type."""
46

47
VARCHAR: int = 1043
48
"""Variable-length character type with length limit."""
49

50
CHAR: int = 1042
51
"""Fixed-length character type."""
52

53
NAME: int = 19
54
"""Internal PostgreSQL name type."""
55

56
CSTRING: int = 2275
57
"""Null-terminated C string type."""
58

59
CSTRING_ARRAY: int = 1263
60
"""Array of C string values."""
61
```
62

63
### Binary Type Constants
64

65
PostgreSQL binary data type identifiers.
66

67
```python { .api }
68
BYTES: int = 17
69
"""Variable-length binary data type (bytea)."""
70

71
BYTES_ARRAY: int = 1001
72
"""Array of bytea values."""
73
```
74

75
### Date and Time Type Constants
76

77
PostgreSQL temporal data type identifiers.
78

79
```python { .api }
80
DATE: int = 1082
81
"""Date type (year, month, day)."""
82

83
TIME: int = 1083
84
"""Time of day type without timezone."""
85

86
TIMESTAMP: int = 1114
87
"""Date and time type without timezone."""
88

89
TIMESTAMPTZ: int = 1184
90
"""Date and time type with timezone."""
91

92
INTERVAL: int = 1186
93
"""Time interval type."""
94

95
TIME_ARRAY: int = 1183
96
"""Array of time values."""
97

98
INTERVAL_ARRAY: int = 1187
99
"""Array of interval values."""
100
```
101

102
### Boolean Type Constants
103

104
PostgreSQL boolean data type identifier.
105

106
```python { .api }
107
BOOLEAN: int = 16
108
"""Boolean true/false type."""
109
```
110

111
### JSON Type Constants
112

113
PostgreSQL JSON data type identifiers.
114

115
```python { .api }
116
JSON: int = 114
117
"""JSON data type (text-based storage)."""
118

119
JSONB: int = 3802
120
"""JSONB data type (binary storage with indexing)."""
121

122
JSON_ARRAY: int = 199
123
"""Array of JSON values."""
124

125
JSONB_ARRAY: int = 3807
126
"""Array of JSONB values."""
127
```
128

129
### Network Type Constants
130

131
PostgreSQL network address data type identifiers.
132

133
```python { .api }
134
INET: int = 869
135
"""IPv4 or IPv6 network address type."""
136

137
CIDR: int = 650
138
"""IPv4 or IPv6 network specification type."""
139

140
MACADDR: int = 829
141
"""MAC address type."""
142

143
CIDR_ARRAY: int = 651
144
"""Array of CIDR values."""
145

146
INET_ARRAY: int = 1041
147
"""Array of INET values."""
148
```
149

150
### Range Type Constants
151

152
PostgreSQL range data type identifiers for various base types.
153

154
```python { .api }
155
INT4RANGE: int = 3904
156
"""Range of integer values."""
157

158
INT8RANGE: int = 3926
159
"""Range of bigint values."""
160

161
NUMRANGE: int = 3906
162
"""Range of numeric values."""
163

164
TSRANGE: int = 3908
165
"""Range of timestamp values."""
166

167
TSTZRANGE: int = 3910
168
"""Range of timestamp with timezone values."""
169

170
DATERANGE: int = 3912
171
"""Range of date values."""
172

173
# Range Arrays
174
INT4RANGE_ARRAY: int = 3905
175
"""Array of int4range values."""
176

177
INT8RANGE_ARRAY: int = 3927
178
"""Array of int8range values."""
179

180
NUMRANGE_ARRAY: int = 3907
181
"""Array of numrange values."""
182

183
TSRANGE_ARRAY: int = 3909
184
"""Array of tsrange values."""
185

186
TSTZRANGE_ARRAY: int = 3911
187
"""Array of tstzrange values."""
188

189
DATERANGE_ARRAY: int = 3913
190
"""Array of daterange values."""
191

192
# Multirange Types (PostgreSQL 14+)
193
INT4MULTIRANGE: int = 4451
194
"""Multirange of integer values."""
195

196
INT8MULTIRANGE: int = 4536
197
"""Multirange of bigint values."""
198

199
NUMMULTIRANGE: int = 4532
200
"""Multirange of numeric values."""
201

202
TSMULTIRANGE: int = 4533
203
"""Multirange of timestamp values."""
204

205
TSTZMULTIRANGE: int = 4534
206
"""Multirange of timestamp with timezone values."""
207

208
DATEMULTIRANGE: int = 4535
209
"""Multirange of date values."""
210

211
# Multirange Arrays
212
INT4MULTIRANGE_ARRAY: int = 6150
213
"""Array of int4multirange values."""
214

215
INT8MULTIRANGE_ARRAY: int = 6157
216
"""Array of int8multirange values."""
217

218
NUMMULTIRANGE_ARRAY: int = 6151
219
"""Array of nummultirange values."""
220

221
TSMULTIRANGE_ARRAY: int = 6152
222
"""Array of tsmultirange values."""
223

224
TSTZMULTIRANGE_ARRAY: int = 6153
225
"""Array of tstzmultirange values."""
226

227
DATEMULTIRANGE_ARRAY: int = 6155
228
"""Array of datemultirange values."""
229
```
230

231
### Array Type Constants
232

233
Array variants for all base PostgreSQL data types.
234

235
```python { .api }
236
# Numeric arrays
237
INTEGER_ARRAY: int = 1007
238
"""Array of integer values."""
239

240
BIGINT_ARRAY: int = 1016
241
"""Array of bigint values."""
242

243
SMALLINT_ARRAY: int = 1005
244
"""Array of smallint values."""
245

246
FLOAT_ARRAY: int = 1022
247
"""Array of double precision float values."""
248

249
REAL_ARRAY: int = 1021
250
"""Array of single precision float values."""
251

252
NUMERIC_ARRAY: int = 1231
253
"""Array of numeric values."""
254

255
# Text arrays
256
TEXT_ARRAY: int = 1009
257
"""Array of text values."""
258

259
VARCHAR_ARRAY: int = 1015
260
"""Array of varchar values."""
261

262
CHAR_ARRAY: int = 1014
263
"""Array of char values."""
264

265
NAME_ARRAY: int = 1003
266
"""Array of name values."""
267

268
# Other arrays
269
BOOLEAN_ARRAY: int = 1000
270
"""Array of boolean values."""
271

272
DATE_ARRAY: int = 1182
273
"""Array of date values."""
274

275
TIMESTAMP_ARRAY: int = 1115
276
"""Array of timestamp values."""
277

278
TIMESTAMPTZ_ARRAY: int = 1185
279
"""Array of timestamp with timezone values."""
280
```
281

282
### Other Type Constants
283

284
Additional PostgreSQL data type identifiers.
285

286
```python { .api }
287
UUID_TYPE: int = 2950
288
"""Universally unique identifier type."""
289

290
UUID_ARRAY: int = 2951
291
"""Array of UUID values."""
292

293
OID: int = 26
294
"""Object identifier type."""
295

296
XID: int = 28
297
"""Transaction identifier type."""
298

299
POINT: int = 600
300
"""Geometric point type."""
301

302
UNKNOWN: int = 705
303
"""Unknown type placeholder."""
304

305
NULLTYPE: int = -1
306
"""Null type identifier."""
307

308
INT2VECTOR: int = 22
309
"""Vector of 16-bit integers type."""
310

311
RECORD: int = 2249
312
"""Anonymous record type."""
313

314
ANY_ARRAY: int = 2277
315
"""Pseudo-type representing any array."""
316
```
317

318
### Basic Input Converter Functions
319

320
Functions for converting PostgreSQL data to Python objects.
321

322
```python { .api }
323
def bool_in(data: str) -> bool:
324
    """Convert PostgreSQL boolean string to Python bool."""
325

326
def bytes_in(data: str) -> bytes:
327
    """Convert PostgreSQL bytea hex string to Python bytes."""
328

329
def cidr_in(data: str) -> ipaddress.IPv4Network | ipaddress.IPv6Network | ipaddress.IPv4Address | ipaddress.IPv6Address:
330
    """Convert PostgreSQL CIDR/INET string to Python IP object."""
331

332
def date_in(data: str) -> datetime.date | str:
333
    """Convert PostgreSQL date string to Python date (or string for infinity)."""
334

335
def inet_in(data: str) -> ipaddress.IPv4Network | ipaddress.IPv6Network | ipaddress.IPv4Address | ipaddress.IPv6Address:
336
    """Convert PostgreSQL INET string to Python IP object."""
337

338
def int_in(data: str) -> int:
339
    """Convert PostgreSQL integer string to Python int."""
340

341
def interval_in(data: str) -> datetime.timedelta | PGInterval:
342
    """Convert PostgreSQL interval string to Python timedelta or PGInterval."""
343

344
def json_in(data: str) -> object:
345
    """Convert PostgreSQL JSON string to Python object."""
346

347
def numeric_in(data: str) -> decimal.Decimal:
348
    """Convert PostgreSQL numeric string to Python Decimal."""
349

350
def point_in(data: str) -> tuple:
351
    """Convert PostgreSQL point string to Python tuple (x, y)."""
352

353
def string_in(data: str) -> str:
354
    """Pass-through for string data."""
355

356
def time_in(data: str) -> datetime.time:
357
    """Convert PostgreSQL time string to Python time."""
358

359
def timestamp_in(data: str) -> datetime.datetime | str:
360
    """Convert PostgreSQL timestamp string to Python datetime (or string for infinity)."""
361

362
def timestamptz_in(data: str) -> datetime.datetime | str:
363
    """Convert PostgreSQL timestamptz string to Python datetime with timezone."""
364

365
def uuid_in(data: str) -> uuid.UUID:
366
    """Convert PostgreSQL UUID string to Python UUID."""
367

368
def vector_in(data: str) -> list:
369
    """Convert PostgreSQL int vector to Python list."""
370

371
def record_in(data: str) -> tuple:
372
    """Convert PostgreSQL record/tuple string to Python tuple."""
373
```
374

375
### Basic Output Converter Functions
376

377
Functions for converting Python objects to PostgreSQL data.
378

379
```python { .api }
380
def bool_out(v: bool) -> str:
381
    """Convert Python bool to PostgreSQL boolean string."""
382

383
def bytes_out(v: bytes) -> str:
384
    """Convert Python bytes to PostgreSQL bytea hex string."""
385

386
def cidr_out(v) -> str:
387
    """Convert Python IP object to PostgreSQL CIDR string."""
388

389
def date_out(v: datetime.date) -> str:
390
    """Convert Python date to PostgreSQL date string."""
391

392
def datetime_out(v: datetime.datetime) -> str:
393
    """Convert Python datetime to PostgreSQL timestamp string."""
394

395
def enum_out(v: Enum) -> str:
396
    """Convert Python enum to PostgreSQL enum string."""
397

398
def float_out(v: float) -> str:
399
    """Convert Python float to PostgreSQL float string."""
400

401
def inet_out(v) -> str:
402
    """Convert Python IP object to PostgreSQL INET string."""
403

404
def int_out(v: int) -> str:
405
    """Convert Python int to PostgreSQL integer string."""
406

407
def interval_out(v: datetime.timedelta) -> str:
408
    """Convert Python timedelta to PostgreSQL interval string."""
409

410
def json_out(v: object) -> str:
411
    """Convert Python object to PostgreSQL JSON string."""
412

413
def numeric_out(d: decimal.Decimal) -> str:
414
    """Convert Python Decimal to PostgreSQL numeric string."""
415

416
def pg_interval_out(v: PGInterval) -> str:
417
    """Convert PGInterval to PostgreSQL interval string."""
418

419
def range_out(v: Range) -> str:
420
    """Convert Python Range to PostgreSQL range string."""
421

422
def string_out(v: str) -> str:
423
    """Pass-through for string data."""
424

425
def time_out(v: datetime.time) -> str:
426
    """Convert Python time to PostgreSQL time string."""
427

428
def uuid_out(v: uuid.UUID) -> str:
429
    """Convert Python UUID to PostgreSQL UUID string."""
430
```
431

432
### Range and Multirange Converter Functions
433

434
Functions for converting PostgreSQL range and multirange types.
435

436
```python { .api }
437
# Range Input Functions
438
def daterange_in(data: str) -> Range:
439
    """Convert PostgreSQL daterange to Python Range with date bounds."""
440

441
def int4range_in(data: str) -> Range:
442
    """Convert PostgreSQL int4range to Python Range with integer bounds."""
443

444
def int8range_in(data: str) -> Range:
445
    """Convert PostgreSQL int8range to Python Range with integer bounds."""
446

447
def numrange_in(data: str) -> Range:
448
    """Convert PostgreSQL numrange to Python Range with Decimal bounds."""
449

450
def tsrange_in(data: str) -> Range:
451
    """Convert PostgreSQL tsrange to Python Range with timestamp bounds."""
452

453
def tstzrange_in(data: str) -> Range:
454
    """Convert PostgreSQL tstzrange to Python Range with timestamptz bounds."""
455

456
# Multirange Input Functions
457
def datemultirange_in(data: str) -> list:
458
    """Convert PostgreSQL datemultirange to list of date ranges."""
459

460
def int4multirange_in(data: str) -> list:
461
    """Convert PostgreSQL int4multirange to list of integer ranges."""
462

463
def int8multirange_in(data: str) -> list:
464
    """Convert PostgreSQL int8multirange to list of integer ranges."""
465

466
def nummultirange_in(data: str) -> list:
467
    """Convert PostgreSQL nummultirange to list of numeric ranges."""
468

469
def tsmultirange_in(data: str) -> list:
470
    """Convert PostgreSQL tsmultirange to list of timestamp ranges."""
471

472
def tstzmultirange_in(data: str) -> list:
473
    """Convert PostgreSQL tstzmultirange to list of timestamptz ranges."""
474
```
475

476
### Array Converter Functions
477

478
Functions for converting PostgreSQL array types to Python lists.
479

480
```python { .api }
481
def bool_array_in(data: str) -> list:
482
    """Convert PostgreSQL bool[] to Python list of bools."""
483

484
def bytes_array_in(data: str) -> list:
485
    """Convert PostgreSQL bytea[] to Python list of bytes."""
486

487
def date_array_in(data: str) -> list:
488
    """Convert PostgreSQL date[] to Python list of dates."""
489

490
def float_array_in(data: str) -> list:
491
    """Convert PostgreSQL float[] to Python list of floats."""
492

493
def int_array_in(data: str) -> list:
494
    """Convert PostgreSQL int[] to Python list of integers."""
495

496
def json_array_in(data: str) -> list:
497
    """Convert PostgreSQL json[] to Python list of JSON objects."""
498

499
def numeric_array_in(data: str) -> list:
500
    """Convert PostgreSQL numeric[] to Python list of Decimals."""
501

502
def string_array_in(data: str) -> list:
503
    """Convert PostgreSQL text[] to Python list of strings."""
504

505
def timestamp_array_in(data: str) -> list:
506
    """Convert PostgreSQL timestamp[] to Python list of timestamps."""
507

508
def uuid_array_in(data: str) -> list:
509
    """Convert PostgreSQL uuid[] to Python list of UUIDs."""
510

511
# Additional specialized array converters available for all supported types
512
```
513

514
### Utility Functions
515

516
Helper functions for SQL formatting and parameter conversion.
517

518
```python { .api }
519
def identifier(sql: str) -> str:
520
    """Escape SQL identifier for safe use in queries."""
521

522
@singledispatch
523
def literal(value: object) -> str:
524
    """
525
    Create SQL literal representation of Python value.
526
    
527
    Supports type-specific formatting via singledispatch for:
528
    - None: Returns "NULL"
529
    - bool: Returns "TRUE" or "FALSE"  
530
    - int/float/Decimal: Returns string representation
531
    - bytes/bytearray: Returns hex format with E'' wrapper
532
    - datetime types: Returns quoted strings
533
    - list: Returns quoted array string
534
    - str: Returns escaped and quoted string
535
    """
536

537
@singledispatch
538
def array_out(val: object) -> str:
539
    """
540
    Convert Python values to PostgreSQL array format.
541
    
542
    Supports type-specific formatting via singledispatch for:
543
    - list: Converts to PostgreSQL array string
544
    - tuple: Converts to PostgreSQL composite string
545
    - None: Returns "NULL"
546
    - dict: Converts to escaped JSON string
547
    - bytes/bytearray: Converts to quoted hex string
548
    - str: Converts with proper escaping
549
    """
550

551
@singledispatch  
552
def composite_out(val: object) -> str:
553
    """Convert Python values to PostgreSQL composite format."""
554

555
def array_string_escape(v: str) -> str:
556
    """Escape strings for PostgreSQL array format."""
557

558
def make_param(py_types: dict, value: object) -> str:
559
    """Convert Python value using appropriate converter."""
560

561
def make_params(py_types: dict, values) -> tuple:
562
    """Convert multiple Python values using appropriate converters."""
563
```
564

565
### Time and Date Utility Functions
566

567
Specialized functions for PostgreSQL interval and time handling.
568

569
```python { .api }
570
def pginterval_in(data: bytes) -> PGInterval:
571
    """
572
    Parse PostgreSQL interval data into PGInterval object.
573
    
574
    Parameters:
575
    - data: Raw PostgreSQL interval data
576
    
577
    Returns:
578
    PGInterval object representing the interval
579
    """
580

581
def pginterval_out(v: PGInterval) -> bytes:
582
    """
583
    Format PGInterval object for PostgreSQL storage.
584
    
585
    Parameters:
586
    - v: PGInterval object to format
587
    
588
    Returns:
589
    Formatted interval data for PostgreSQL
590
    """
591

592
def timedelta_in(data: bytes) -> datetime.timedelta:
593
    """
594
    Convert PostgreSQL interval to Python timedelta.
595
    
596
    Parameters:
597
    - data: Raw PostgreSQL interval data
598
    
599
    Returns:
600
    Python timedelta object
601
    """
602
```
603

604
### Type Aliases and Legacy Constants
605

606
Legacy type name aliases for backward compatibility.
607

608
```python { .api }
609
# Legacy numeric aliases
610
BIGINTEGER: int = BIGINT
611
"""Alias for BIGINT type."""
612

613
DATETIME: int = TIMESTAMP
614
"""Alias for TIMESTAMP type."""
615

616
NUMBER: int = NUMERIC
617
"""Alias for NUMERIC type."""
618

619
DECIMAL: int = NUMERIC
620
"""Alias for NUMERIC type."""
621

622
DECIMAL_ARRAY: int = NUMERIC_ARRAY
623
"""Alias for NUMERIC_ARRAY type."""
624

625
ROWID: int = OID
626
"""Alias for OID type."""
627

628
TIMEDELTA: int = INTERVAL
629
"""Alias for INTERVAL type."""
630

631
STRING: int = VARCHAR
632
"""Alias for VARCHAR type."""
633
```
634

635
### Usage Examples
636

637
#### Working with Different Data Types
638

639
```python
640
import pg8000
641
import datetime
642
import decimal
643
import uuid
644
import ipaddress
645

646
conn = pg8000.connect(user="myuser", password="mypass", database="mydb")
647

648
# Insert various data types
649
cursor = conn.cursor()
650
cursor.execute("""
651
    INSERT INTO test_types (
652
        int_col, bigint_col, numeric_col, float_col,
653
        text_col, bool_col, date_col, timestamp_col,
654
        json_col, uuid_col, inet_col, bytea_col
655
    ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
656
""", (
657
    42,                              # INTEGER
658
    9223372036854775807,             # BIGINT
659
    decimal.Decimal('123.456'),      # NUMERIC
660
    3.14159,                         # FLOAT
661
    "Hello, PostgreSQL!",            # TEXT
662
    True,                            # BOOLEAN
663
    datetime.date(2023, 12, 25),     # DATE
664
    datetime.datetime.now(),         # TIMESTAMP
665
    {"key": "value", "array": [1,2,3]},  # JSON
666
    uuid.uuid4(),                    # UUID
667
    ipaddress.IPv4Address('192.168.1.1'),  # INET
668
    b"binary data"                   # BYTEA
669
))
670

671
conn.commit()
672
cursor.close()
673
conn.close()
674
```
675

676
#### Working with Arrays
677

678
```python
679
import pg8000
680

681
conn = pg8000.connect(user="myuser", password="mypass", database="mydb")
682
cursor = conn.cursor()
683

684
# Insert array data
685
cursor.execute("""
686
    INSERT INTO test_arrays (
687
        int_array, text_array, bool_array
688
    ) VALUES (%s, %s, %s)
689
""", (
690
    [1, 2, 3, 4, 5],                    # INTEGER_ARRAY
691
    ["apple", "banana", "cherry"],       # TEXT_ARRAY
692
    [True, False, True, False]           # BOOLEAN_ARRAY
693
))
694

695
# Query array data
696
cursor.execute("SELECT int_array, text_array FROM test_arrays WHERE id = %s", (1,))
697
row = cursor.fetchone()
698
print(f"Integer array: {row[0]}")
699
print(f"Text array: {row[1]}")
700

701
conn.commit()
702
cursor.close()
703
conn.close()
704
```
705

706
#### Working with JSON Data
707

708
```python
709
import pg8000
710
import json
711

712
conn = pg8000.connect(user="myuser", password="mypass", database="mydb")
713
cursor = conn.cursor()
714

715
# Insert JSON data
716
user_profile = {
717
    "name": "John Doe",
718
    "age": 30,
719
    "interests": ["programming", "music", "travel"],
720
    "address": {
721
        "street": "123 Main St",
722
        "city": "Anytown",
723
        "zip": "12345"
724
    }
725
}
726

727
cursor.execute("""
728
    INSERT INTO user_profiles (user_id, profile_data) 
729
    VALUES (%s, %s)
730
""", (123, user_profile))
731

732
# Query JSON data with JSON operators
733
cursor.execute("""
734
    SELECT profile_data->>'name' as name,
735
           profile_data->'interests' as interests
736
    FROM user_profiles 
737
    WHERE user_id = %s
738
""", (123,))
739

740
row = cursor.fetchone()
741
print(f"Name: {row[0]}")
742
print(f"Interests: {row[1]}")
743

744
conn.commit()
745
cursor.close()
746
conn.close()
747
```