0
# Custom Types
1

2
Custom Python classes for PostgreSQL-specific data types including intervals and ranges that provide rich functionality beyond basic Python types.
3

4
## Capabilities
5

6
### PGInterval Class
7

8
Python representation of PostgreSQL interval data type with support for multiple input formats and conversions.
9

10
```python { .api }
11
class PGInterval:
12
    """
13
    PostgreSQL interval type with comprehensive time component support.
14
    
15
    Properties:
16
    - millennia: int - Number of millennia
17
    - centuries: int - Number of centuries  
18
    - decades: int - Number of decades
19
    - years: int - Number of years
20
    - months: int - Number of months
21
    - weeks: int - Number of weeks
22
    - days: int - Number of days
23
    - hours: int - Number of hours
24
    - minutes: int - Number of minutes
25
    - seconds: int - Number of seconds
26
    - microseconds: int - Number of microseconds
27
    """
28
    
29
    def __init__(
30
        self,
31
        millennia: int = 0,
32
        centuries: int = 0,
33
        decades: int = 0,
34
        years: int = 0,
35
        months: int = 0,
36
        weeks: int = 0,
37
        days: int = 0,
38
        hours: int = 0,
39
        minutes: int = 0,
40
        seconds: int = 0,
41
        microseconds: int = 0
42
    ):
43
        """
44
        Create PGInterval with specified time components.
45
        
46
        Parameters:
47
        - millennia: Number of millennia
48
        - centuries: Number of centuries
49
        - decades: Number of decades
50
        - years: Number of years
51
        - months: Number of months
52
        - weeks: Number of weeks
53
        - days: Number of days
54
        - hours: Number of hours
55
        - minutes: Number of minutes
56
        - seconds: Number of seconds
57
        - microseconds: Number of microseconds
58
        """
59
    
60
    @classmethod
61
    def from_str(cls, interval_str: str) -> PGInterval:
62
        """
63
        Parse interval from string representation.
64
        
65
        Parameters:
66
        - interval_str: String representation of interval
67
        
68
        Returns:
69
        PGInterval object parsed from string
70
        
71
        Raises:
72
        ValueError: If string format is invalid
73
        """
74
    
75
    @classmethod
76
    def from_str_iso_8601(cls, interval_str: str) -> PGInterval:
77
        """
78
        Parse interval from ISO 8601 duration format.
79
        
80
        Parameters:
81
        - interval_str: ISO 8601 duration string (e.g., "P1Y2M3DT4H5M6S")
82
        
83
        Returns:
84
        PGInterval object parsed from ISO 8601 format
85
        
86
        Raises:
87
        ValueError: If ISO 8601 format is invalid
88
        """
89
    
90
    @classmethod
91
    def from_str_postgres(cls, interval_str: str) -> PGInterval:
92
        """
93
        Parse interval from PostgreSQL native format.
94
        
95
        Parameters:
96
        - interval_str: PostgreSQL interval string (e.g., "1 year 2 months")
97
        
98
        Returns:
99
        PGInterval object parsed from PostgreSQL format
100
        
101
        Raises:
102
        ValueError: If PostgreSQL format is invalid
103
        """
104
    
105
    @classmethod
106
    def from_str_sql_standard(cls, interval_str: str) -> PGInterval:
107
        """
108
        Parse interval from SQL standard format.
109
        
110
        Parameters:
111
        - interval_str: SQL standard interval string
112
        
113
        Returns:
114
        PGInterval object parsed from SQL standard format
115
        
116
        Raises:
117
        ValueError: If SQL standard format is invalid
118
        """
119
    
120
    def normalize(self) -> PGInterval:
121
        """
122
        Normalize interval to standard units.
123
        
124
        Converts larger units to smaller units where possible
125
        (e.g., 24 hours to 1 day).
126
        
127
        Returns:
128
        New normalized PGInterval object
129
        """
130
    
131
    def to_timedelta(self) -> datetime.timedelta:
132
        """
133
        Convert to Python timedelta object.
134
        
135
        Note: Only day, hour, minute, second, and microsecond components
136
        are included as timedelta doesn't support months/years.
137
        
138
        Returns:
139
        Python timedelta object with convertible components
140
        
141
        Raises:
142
        ValueError: If interval contains non-convertible components
143
        """
144
    
145
    def __str__(self) -> str:
146
        """
147
        String representation of interval.
148
        
149
        Returns:
150
        Human-readable interval string
151
        """
152
    
153
    def __repr__(self) -> str:
154
        """
155
        Developer representation of interval.
156
        
157
        Returns:
158
        Detailed interval representation
159
        """
160
    
161
    def __eq__(self, other: object) -> bool:
162
        """
163
        Test equality with another PGInterval.
164
        
165
        Parameters:
166
        - other: Object to compare with
167
        
168
        Returns:
169
        True if intervals are equal
170
        """
171
```
172

173
### Range Class
174

175
Python representation of PostgreSQL range data types with support for inclusive/exclusive bounds.
176

177
```python { .api }
178
class Range:
179
    """
180
    PostgreSQL range type supporting various boundary conditions.
181
    
182
    Properties:
183
    - lower: object - Lower bound value (None for unbounded)
184
    - upper: object - Upper bound value (None for unbounded)  
185
    - bounds: str - Boundary inclusion string ("[)", "[]", "(]", "()")
186
    - is_empty: bool - Whether range is empty
187
    """
188
    
189
    def __init__(
190
        self,
191
        lower: object = None,
192
        upper: object = None,
193
        bounds: str = "[)",
194
        is_empty: bool = False
195
    ):
196
        """
197
        Create range with specified bounds and inclusivity.
198
        
199
        Parameters:
200
        - lower: Lower bound value (None for unbounded)
201
        - upper: Upper bound value (None for unbounded)
202
        - bounds: Boundary specification string:
203
          - "[)" : Lower inclusive, upper exclusive (default)
204
          - "[]" : Both bounds inclusive
205
          - "(]" : Lower exclusive, upper inclusive  
206
          - "()" : Both bounds exclusive
207
        - is_empty: Whether to create an empty range
208
        
209
        Raises:
210
        ValueError: If bounds format is invalid
211
        """
212
    
213
    @property
214
    def lower_inc(self) -> bool:
215
        """
216
        Whether lower bound is inclusive.
217
        
218
        Returns:
219
        True if lower bound is inclusive
220
        """
221
    
222
    @property
223
    def upper_inc(self) -> bool:
224
        """
225
        Whether upper bound is inclusive.
226
        
227
        Returns:
228
        True if upper bound is inclusive
229
        """
230
    
231
    @property
232
    def lower_inf(self) -> bool:
233
        """
234
        Whether lower bound is infinite (unbounded).
235
        
236
        Returns:
237
        True if lower bound is infinite
238
        """
239
    
240
    @property  
241
    def upper_inf(self) -> bool:
242
        """
243
        Whether upper bound is infinite (unbounded).
244
        
245
        Returns:
246
        True if upper bound is infinite
247
        """
248
    
249
    def __contains__(self, value: object) -> bool:
250
        """
251
        Test if value is contained in range.
252
        
253
        Parameters:
254
        - value: Value to test for containment
255
        
256
        Returns:
257
        True if value is within range bounds
258
        """
259
    
260
    def __str__(self) -> str:
261
        """
262
        String representation of range.
263
        
264
        Returns:
265
        Range string in PostgreSQL format
266
        """
267
    
268
    def __repr__(self) -> str:
269
        """
270
        Developer representation of range.
271
        
272
        Returns:
273
        Detailed range representation
274
        """
275
    
276
    def __eq__(self, other: object) -> bool:
277
        """
278
        Test equality with another Range.
279
        
280
        Parameters:
281
        - other: Object to compare with
282
        
283
        Returns:
284
        True if ranges are equal
285
        """
286
```
287

288
### Usage Examples
289

290
#### Working with PGInterval
291

292
```python
293
import pg8000
294
from pg8000 import PGInterval
295
import datetime
296

297
# Create intervals using different methods
298
interval1 = PGInterval(years=1, months=6, days=15, hours=3, minutes=30)
299
interval2 = PGInterval.from_str_iso_8601("P1Y6M15DT3H30M")
300
interval3 = PGInterval.from_str_postgres("1 year 6 months 15 days 03:30:00")
301

302
print(f"Interval 1: {interval1}")
303
print(f"Interval 2: {interval2}")
304
print(f"Interval 3: {interval3}")
305

306
# Normalize interval
307
normalized = interval1.normalize()
308
print(f"Normalized: {normalized}")
309

310
# Convert to timedelta (only day/time components)
311
try:
312
    td = PGInterval(days=5, hours=3, minutes=30).to_timedelta()
313
    print(f"As timedelta: {td}")
314
except ValueError as e:
315
    print(f"Cannot convert: {e}")
316

317
# Use with database
318
conn = pg8000.connect(user="myuser", password="mypass", database="mydb")
319
cursor = conn.cursor()
320

321
cursor.execute("""
322
    INSERT INTO events (name, duration) 
323
    VALUES (%s, %s)
324
""", ("Long Process", interval1))
325

326
cursor.execute("SELECT name, duration FROM events WHERE id = %s", (1,))
327
row = cursor.fetchone()
328
print(f"Event: {row[0]}, Duration: {row[1]}")
329

330
cursor.close()
331
conn.close()
332
```
333

334
#### Working with Range Types
335

336
```python
337
import pg8000
338
from pg8000 import Range
339
import datetime
340

341
# Create different types of ranges
342
int_range = Range(lower=10, upper=20, bounds="[)")  # [10, 20)
343
date_range = Range(
344
    lower=datetime.date(2023, 1, 1),
345
    upper=datetime.date(2023, 12, 31),
346
    bounds="[]"  # [2023-01-01, 2023-12-31]
347
)
348
unbounded_range = Range(lower=100, upper=None, bounds="[)")  # [100, ∞)
349
empty_range = Range(is_empty=True)
350

351
print(f"Integer range: {int_range}")
352
print(f"Date range: {date_range}")
353
print(f"Unbounded range: {unbounded_range}")
354
print(f"Empty range: {empty_range}")
355

356
# Test containment
357
print(f"15 in int_range: {15 in int_range}")          # True
358
print(f"20 in int_range: {20 in int_range}")          # False (exclusive upper)
359
print(f"5 in int_range: {5 in int_range}")            # False
360

361
# Check boundary properties
362
print(f"Lower inclusive: {int_range.lower_inc}")      # True
363
print(f"Upper inclusive: {int_range.upper_inc}")      # False
364
print(f"Upper infinite: {unbounded_range.upper_inf}") # True
365

366
# Use with database
367
conn = pg8000.connect(user="myuser", password="mypass", database="mydb")
368
cursor = conn.cursor()
369

370
cursor.execute("""
371
    CREATE TABLE IF NOT EXISTS price_ranges (
372
        id SERIAL PRIMARY KEY,
373
        category TEXT,
374
        price_range int4range
375
    )
376
""")
377

378
cursor.execute("""
379
    INSERT INTO price_ranges (category, price_range) 
380
    VALUES (%s, %s)
381
""", ("Budget", int_range))
382

383
cursor.execute("""
384
    SELECT category, price_range 
385
    FROM price_ranges 
386
    WHERE price_range @> %s
387
""", (15,))  # Find ranges containing value 15
388

389
row = cursor.fetchone()
390
if row:
391
    print(f"Category: {row[0]}, Range: {row[1]}")
392

393
cursor.close()
394
conn.close()
395
```
396

397
#### Advanced Range Operations
398

399
```python
400
import pg8000
401
from pg8000 import Range
402
import datetime
403

404
conn = pg8000.connect(user="myuser", password="mypass", database="mydb")
405
cursor = conn.cursor()
406

407
# Create table with different range types
408
cursor.execute("""
409
    CREATE TABLE IF NOT EXISTS bookings (
410
        id SERIAL PRIMARY KEY,
411
        room_number INT,
412
        date_range daterange,
413
        time_range tsrange,
414
        price_range numrange
415
    )
416
""")
417

418
# Insert booking with multiple range types
419
date_range = Range(
420
    lower=datetime.date(2023, 12, 20),
421
    upper=datetime.date(2023, 12, 25),
422
    bounds="[]"
423
)
424

425
time_range = Range(
426
    lower=datetime.datetime(2023, 12, 20, 14, 0),
427
    upper=datetime.datetime(2023, 12, 25, 12, 0),
428
    bounds="[)"
429
)
430

431
price_range = Range(lower=100.00, upper=500.00, bounds="[]")
432

433
cursor.execute("""
434
    INSERT INTO bookings (room_number, date_range, time_range, price_range)
435
    VALUES (%s, %s, %s, %s)
436
""", (101, date_range, time_range, price_range))
437

438
# Query overlapping ranges
439
cursor.execute("""
440
    SELECT room_number, date_range 
441
    FROM bookings 
442
    WHERE date_range && %s
443
""", (Range(
444
    lower=datetime.date(2023, 12, 22), 
445
    upper=datetime.date(2023, 12, 28),
446
    bounds="[]"
447
),))
448

449
for row in cursor.fetchall():
450
    print(f"Room {row[0]} booked during: {row[1]}")
451

452
cursor.close()
453
conn.close()
454
```