0
# Data Types
1

2
Comprehensive data type support including DB-API 2.0 type constructors, Vertica-specific types, and customizable type conversion system. Handles complex types like arrays, sets, and custom data structures with utilities for type inspection and conversion.
3

4
## Capabilities
5

6
### DB-API 2.0 Type Constructors
7

8
Standard type constructors for creating database-compatible objects.
9

10
```python { .api }
11
def Date(year: int, month: int, day: int):
12
    """
13
    Construct a date object.
14
    
15
    Parameters:
16
    - year (int): Year value
17
    - month (int): Month value (1-12)
18
    - day (int): Day value (1-31)
19
    
20
    Returns:
21
    datetime.date: Date object
22
    """
23

24
def Time(hour: int, minute: int, second: int):
25
    """
26
    Construct a time object.
27
    
28
    Parameters:
29
    - hour (int): Hour value (0-23)
30
    - minute (int): Minute value (0-59)
31
    - second (int): Second value (0-59)
32
    
33
    Returns:
34
    datetime.time: Time object
35
    """
36

37
def Timestamp(year: int, month: int, day: int, hour: int, minute: int, second: int):
38
    """
39
    Construct a timestamp object.
40
    
41
    Parameters:
42
    - year (int): Year value
43
    - month (int): Month value (1-12)
44
    - day (int): Day value (1-31)
45
    - hour (int): Hour value (0-23)
46
    - minute (int): Minute value (0-59)
47
    - second (int): Second value (0-59)
48
    
49
    Returns:
50
    datetime.datetime: Timestamp object
51
    """
52

53
def DateFromTicks(ticks: float):
54
    """
55
    Construct a date from Unix timestamp.
56
    
57
    Parameters:
58
    - ticks (float): Unix timestamp (seconds since epoch)
59
    
60
    Returns:
61
    datetime.date: Date object
62
    """
63

64
def TimeFromTicks(ticks: float):
65
    """
66
    Construct a time from Unix timestamp.
67
    
68
    Parameters:
69
    - ticks (float): Unix timestamp (seconds since epoch)
70
    
71
    Returns:
72
    datetime.time: Time object
73
    """
74

75
def TimestampFromTicks(ticks: float):
76
    """
77
    Construct a timestamp from Unix timestamp.
78
    
79
    Parameters:
80
    - ticks (float): Unix timestamp (seconds since epoch)
81
    
82
    Returns:
83
    datetime.time: Time object (Note: This appears to be a bug in the source code - should return datetime)
84
    """
85

86
def Binary(string):
87
    """
88
    Construct a binary object for handling binary data.
89
    
90
    Parameters:
91
    - string: Binary string data
92
    
93
    Returns:
94
    Bytea: Binary object wrapper
95
    """
96
```
97

98
### DB-API 2.0 Type Objects
99

100
Type objects for column type comparison and identification.
101

102
```python { .api }
103
STRING = VerticaType([VerticaType.CHAR, VerticaType.VARCHAR, VerticaType.BINARY,
104
                     VerticaType.VARBINARY, VerticaType.UNKNOWN,
105
                     VerticaType.LONGVARBINARY, VerticaType.LONGVARCHAR])
106
"""Type object for string-based column types."""
107

108
BINARY = VerticaType([VerticaType.BINARY, VerticaType.VARBINARY, VerticaType.LONGVARBINARY])
109
"""Type object for binary column types."""
110

111
NUMBER = VerticaType([VerticaType.INT8, VerticaType.FLOAT8, VerticaType.NUMERIC])
112
"""Type object for numeric column types."""
113

114
DATETIME = VerticaType([VerticaType.DATE, VerticaType.TIME, VerticaType.TIMETZ,
115
                       VerticaType.TIMESTAMP, VerticaType.TIMESTAMPTZ,
116
                       VerticaType.INTERVAL, VerticaType.INTERVALYM])
117
"""Type object for date/time column types."""
118

119
ROWID = VerticaType([])
120
"""Type object for row ID types (empty for Vertica)."""
121
```
122

123
### VerticaType Class
124

125
Type constants and comparison functionality for all Vertica data types.
126

127
```python { .api }
128
class VerticaType:
129
    """
130
    Type constants and comparison class for Vertica data types.
131
    Contains all Vertica type OID constants and comparison methods.
132
    """
133
    
134
    # Basic Types
135
    UNKNOWN = 4
136
    BOOL = 5
137
    INT8 = 6
138
    FLOAT8 = 7
139
    CHAR = 8
140
    VARCHAR = 9
141
    DATE = 10
142
    TIME = 11
143
    TIMESTAMP = 12
144
    TIMESTAMPTZ = 13
145
    INTERVAL = 14
146
    INTERVALYM = 114
147
    TIMETZ = 15
148
    NUMERIC = 16
149
    VARBINARY = 17
150
    UUID = 20
151
    LONGVARCHAR = 115
152
    LONGVARBINARY = 116
153
    BINARY = 117
154
    
155
    # Complex Types
156
    ROW = 300
157
    ARRAY = 301
158
    MAP = 302
159
    
160
    # One-dimensional arrays (selected examples)
161
    ARRAY1D_BOOL = 1505
162
    ARRAY1D_INT8 = 1506
163
    ARRAY1D_FLOAT8 = 1507
164
    # ... (many more array type constants)
165
    
166
    # One-dimensional sets (selected examples)
167
    SET_BOOL = 2705
168
    SET_INT8 = 2706
169
    # ... (many more set type constants)
170
    
171
    def __init__(self, type_list=None):
172
        """
173
        Initialize VerticaType with list of type OIDs.
174
        
175
        Parameters:
176
        - type_list (list, optional): List of type OIDs for comparison
177
        """
178
    
179
    def __eq__(self, other) -> bool:
180
        """
181
        Compare type objects for equality.
182
        
183
        Parameters:
184
        - other: Type OID or VerticaType to compare
185
        
186
        Returns:
187
        bool: True if types match, False otherwise
188
        """
189
    
190
    def __ne__(self, other) -> bool:
191
        """
192
        Compare type objects for inequality.
193
        
194
        Parameters:
195
        - other: Type OID or VerticaType to compare
196
        
197
        Returns:
198
        bool: True if types don't match, False otherwise
199
        """
200
```
201

202
### Bytea Class
203

204
Binary string class for handling binary data with proper encoding.
205

206
```python { .api }
207
class Bytea:
208
    """
209
    Binary string class for handling binary data.
210
    """
211
    
212
    def __init__(self, data):
213
        """
214
        Initialize Bytea object with binary data.
215
        
216
        Parameters:
217
        - data: Binary data (bytes, str, or other binary-compatible type)
218
        """
219
    
220
    def __str__(self) -> str:
221
        """
222
        Get string representation of binary data.
223
        
224
        Returns:
225
        str: String representation
226
        """
227
    
228
    def __repr__(self) -> str:
229
        """
230
        Get detailed string representation.
231
        
232
        Returns:
233
        str: Detailed representation
234
        """
235
    
236
    def __eq__(self, other) -> bool:
237
        """
238
        Compare Bytea objects for equality.
239
        
240
        Parameters:
241
        - other: Other Bytea object to compare
242
        
243
        Returns:
244
        bool: True if equal, False otherwise
245
        """
246
```
247

248
### Type Inspection Utilities
249

250
Utility functions for inspecting and working with Vertica data types.
251

252
```python { .api }
253
def getTypeName(data_type_oid: int, type_modifier: int = -1) -> str:
254
    """
255
    Get human-readable type name from type OID and modifier.
256
    
257
    Parameters:
258
    - data_type_oid (int): Vertica type OID
259
    - type_modifier (int): Type modifier for precision/scale info (default: -1)
260
    
261
    Returns:
262
    str: Human-readable type name (e.g., 'VARCHAR(100)', 'NUMERIC(10,2)')
263
    """
264

265
def getComplexElementType(data_type_oid: int) -> int:
266
    """
267
    Get element type OID for complex types (arrays, sets).
268
    
269
    Parameters:
270
    - data_type_oid (int): Complex type OID
271
    
272
    Returns:
273
    int: Element type OID, or 0 if not a complex type
274
    """
275

276
def getIntervalRange(data_type_oid: int, type_modifier: int) -> str:
277
    """
278
    Get interval range string for interval types.
279
    
280
    Parameters:
281
    - data_type_oid (int): Interval type OID
282
    - type_modifier (int): Type modifier containing range information
283
    
284
    Returns:
285
    str: Interval range string (e.g., 'DAY TO SECOND', 'YEAR TO MONTH')
286
    """
287

288
def getIntervalLeadingPrecision(data_type_oid: int, type_modifier: int) -> int:
289
    """
290
    Get leading precision of interval types.
291
    
292
    Parameters:
293
    - data_type_oid (int): Interval type OID
294
    - type_modifier (int): Type modifier containing precision information
295
    
296
    Returns:
297
    int: Leading precision value
298
    """
299

300
def getPrecision(data_type_oid: int, type_modifier: int) -> int:
301
    """
302
    Get precision of numeric types.
303
    
304
    Parameters:
305
    - data_type_oid (int): Numeric type OID
306
    - type_modifier (int): Type modifier containing precision information
307
    
308
    Returns:
309
    int: Precision value, or -1 if not applicable
310
    """
311

312
def getScale(data_type_oid: int, type_modifier: int) -> int:
313
    """
314
    Get scale of numeric types.
315
    
316
    Parameters:
317
    - data_type_oid (int): Numeric type OID
318
    - type_modifier (int): Type modifier containing scale information
319
    
320
    Returns:
321
    int: Scale value, or -1 if not applicable
322
    """
323

324
def getDisplaySize(data_type_oid: int, type_modifier: int) -> int:
325
    """
326
    Get display size for a type.
327
    
328
    Parameters:
329
    - data_type_oid (int): Type OID
330
    - type_modifier (int): Type modifier
331
    
332
    Returns:
333
    int: Display size in characters, or -1 if not applicable
334
    """
335
```
336

337
## Usage Examples
338

339
### Basic Type Construction
340

341
```python
342
from vertica_python.datatypes import Date, Time, Timestamp, Binary
343

344
# Create date/time objects
345
birthday = Date(1990, 5, 15)
346
meeting_time = Time(14, 30, 0)
347
event_timestamp = Timestamp(2024, 12, 25, 18, 0, 0)
348

349
# Create from Unix timestamps
350
import time
351
from vertica_python.datatypes import DateFromTicks, TimeFromTicks, TimestampFromTicks
352
now_date = DateFromTicks(time.time())
353
now_time = TimeFromTicks(time.time())
354
now_timestamp = TimestampFromTicks(time.time())
355

356
# Handle binary data
357
image_data = b'\x89PNG\r\n\x1a\n...'  # Binary image data
358
binary_obj = Binary(image_data)
359

360
# Use in queries
361
with conn.cursor() as cursor:
362
    cursor.execute(
363
        "INSERT INTO events (name, date, time, timestamp, image) VALUES (:name, :date, :time, :ts, :img)",
364
        {
365
            'name': 'Holiday Party',
366
            'date': birthday,
367
            'time': meeting_time,
368
            'ts': event_timestamp,
369
            'img': binary_obj
370
        }
371
    )
372
```
373

374
### Type Comparison and Detection
375

376
```python
377
from vertica_python.datatypes import getTypeName, STRING, NUMBER, DATETIME, BINARY, VerticaType, getPrecision, getScale
378

379
with conn.cursor() as cursor:
380
    cursor.execute("SELECT name, age, salary, hire_date FROM employees LIMIT 1")
381
    
382
    # Examine column types
383
    for i, col_desc in enumerate(cursor.description):
384
        name, type_code, display_size, internal_size, precision, scale, null_ok = col_desc
385
        
386
        print(f"Column {name}:")
387
        print(f"  Type code: {type_code}")
388
        print(f"  Type name: {getTypeName(type_code, -1)}")
389
        
390
        # Check type categories
391
        if type_code in STRING:
392
            print(f"  Category: String (max length: {display_size})")
393
        elif type_code in NUMBER:
394
            print(f"  Category: Number (precision: {precision}, scale: {scale})")
395
        elif type_code in DATETIME:
396
            print(f"  Category: Date/Time")
397
        elif type_code in BINARY:
398
            print(f"  Category: Binary")
399
        
400
        # Get detailed type information
401
        if type_code == VerticaType.NUMERIC:
402
            print(f"  Precision: {getPrecision(type_code, precision)}")
403
            print(f"  Scale: {getScale(type_code, scale)}")
404
```
405

406
### Working with Complex Types
407

408
```python
409
from vertica_python.datatypes import VerticaType, getComplexElementType, getTypeName
410

411
# Arrays
412
with conn.cursor() as cursor:
413
    cursor.execute("SELECT tags FROM articles WHERE id = 1")
414
    row = cursor.fetchone()
415
    tags = row[0]  # This might be a list like ['python', 'database', 'vertica']
416
    
417
    # Insert array data
418
    cursor.execute(
419
        "INSERT INTO articles (title, tags) VALUES (:title, :tags)",
420
        {
421
            'title': 'Database Guide',
422
            'tags': ['sql', 'database', 'tutorial']
423
        }
424
    )
425

426
# Check if column is a complex type
427
with conn.cursor() as cursor:
428
    cursor.execute("SELECT * FROM articles LIMIT 1")
429
    for col_desc in cursor.description:
430
        name, type_code = col_desc[0], col_desc[1]
431
        
432
        if type_code == VerticaType.ARRAY:
433
            element_type = getComplexElementType(type_code)
434
            element_name = getTypeName(element_type, -1)
435
            print(f"Column {name} is an array of {element_name}")
436
        elif getComplexElementType(type_code) is not None:
437
            element_type = getComplexElementType(type_code)
438
            element_name = getTypeName(element_type, -1)
439
            print(f"Column {name} is a set of {element_name}")
440
```
441

442
### Custom Type Conversion
443

444
```python
445
import decimal
446
from datetime import datetime, timezone
447
from vertica_python.datatypes import VerticaType
448

449
# Custom converter for high-precision decimals
450
def high_precision_decimal_converter(value):
451
    """Convert string to high-precision decimal."""
452
    if value is None:
453
        return None
454
    return decimal.Decimal(value)
455

456
# Custom converter for timezone-aware timestamps
457
def timezone_timestamp_converter(value):
458
    """Convert timestamp to timezone-aware datetime."""
459
    if value is None:
460
        return None
461
    # Assume UTC if no timezone info
462
    if isinstance(value, datetime) and value.tzinfo is None:
463
        return value.replace(tzinfo=timezone.utc)
464
    return value
465

466
with conn.cursor() as cursor:
467
    # Register custom converters
468
    cursor.register_sqldata_converter(
469
        VerticaType.NUMERIC,
470
        high_precision_decimal_converter
471
    )
472
    cursor.register_sqldata_converter(
473
        VerticaType.TIMESTAMPTZ,
474
        timezone_timestamp_converter
475
    )
476
    
477
    # Use custom conversion
478
    cursor.execute("SELECT price, created_at FROM products WHERE id = 1")
479
    price, created_at = cursor.fetchone()
480
    
481
    # price is now a decimal.Decimal with full precision
482
    # created_at is now timezone-aware
483
    
484
    print(f"Price: {price} (type: {type(price)})")
485
    print(f"Created: {created_at} (timezone: {created_at.tzinfo})")
486
```
487

488
### SQL Literal Adapters
489

490
```python
491
import uuid
492
import json
493

494
# Custom adapter for UUID objects
495
def uuid_adapter(obj):
496
    """Convert UUID to SQL literal."""
497
    return f"'{str(obj)}'"
498

499
# Custom adapter for JSON objects
500
def json_adapter(obj):
501
    """Convert dict/list to JSON SQL literal."""
502
    return f"'{json.dumps(obj)}'"
503

504
with conn.cursor() as cursor:
505
    # Register custom adapters
506
    cursor.register_sql_literal_adapter(uuid.UUID, uuid_adapter)
507
    cursor.register_sql_literal_adapter(dict, json_adapter)
508
    cursor.register_sql_literal_adapter(list, json_adapter)
509
    
510
    # Use custom adapters
511
    product_id = uuid.uuid4()
512
    metadata = {'category': 'electronics', 'tags': ['phone', 'mobile']}
513
    
514
    cursor.execute(
515
        "INSERT INTO products (id, name, metadata) VALUES (:id, :name, :metadata)",
516
        {
517
            'id': product_id,  # Automatically converted to SQL string
518
            'name': 'Smartphone',
519
            'metadata': metadata  # Automatically converted to JSON string
520
        }
521
    )
522
```
523

524
### Working with Intervals
525

526
```python
527
from datetime import timedelta
528
from vertica_python.datatypes import VerticaType, getIntervalRange, getIntervalLeadingPrecision
529

530
with conn.cursor() as cursor:
531
    # Query with interval type
532
    cursor.execute("SELECT name, duration FROM events WHERE duration > INTERVAL '1 hour'")
533
    
534
    for row in cursor.fetchall():
535
        name, duration = row
536
        print(f"Event: {name}")
537
        
538
        # Check column metadata for interval details
539
        for col_desc in cursor.description:
540
            col_name, type_code, _, _, precision, scale, _ = col_desc
541
            if col_name == 'duration' and type_code == VerticaType.INTERVAL:
542
                range_str = getIntervalRange(type_code, precision)
543
                leading_precision = getIntervalLeadingPrecision(type_code, precision)
544
                print(f"  Duration: {duration} (range: {range_str}, precision: {leading_precision})")
545

546
# Insert interval data
547
with conn.cursor() as cursor:
548
    # Vertica accepts various interval formats
549
    cursor.execute(
550
        "INSERT INTO events (name, duration) VALUES (:name, :duration)",
551
        {
552
            'name': 'Meeting',
553
            'duration': '2 hours 30 minutes'  # String format
554
        }
555
    )
556
    
557
    # Or using Python timedelta (if converter is set up)
558
    duration = timedelta(hours=2, minutes=30)
559
    cursor.execute(
560
        "INSERT INTO events (name, duration) VALUES (:name, INTERVAL :duration)",
561
        {
562
            'name': 'Workshop',
563
            'duration': f'{duration.total_seconds()} seconds'
564
        }
565
    )
566
```
567

568
### Type Utility Functions
569

570
```python
571
from vertica_python.datatypes import (
572
    getTypeName, NUMBER, STRING, VerticaType, getPrecision, getScale,
573
    getDisplaySize, getIntervalRange, getIntervalLeadingPrecision,
574
    getComplexElementType
575
)
576

577
def analyze_table_schema(cursor, table_name):
578
    """Analyze and display detailed schema information."""
579
    cursor.execute(f"SELECT * FROM {table_name} LIMIT 0")  # Get just metadata
580
    
581
    print(f"Schema for table: {table_name}")
582
    print("-" * 60)
583
    
584
    for col_desc in cursor.description:
585
        name, type_code, display_size, internal_size, precision, scale, null_ok = col_desc
586
        
587
        # Get type name with modifiers
588
        type_name = getTypeName(type_code, precision)
589
        
590
        print(f"Column: {name}")
591
        print(f"  Type: {type_name} (OID: {type_code})")
592
        print(f"  Nullable: {'Yes' if null_ok else 'No'}")
593
        
594
        # Type-specific information
595
        if type_code in NUMBER:
596
            if getPrecision(type_code, precision) is not None:
597
                prec = getPrecision(type_code, precision)
598
                scl = getScale(type_code, scale)
599
                print(f"  Precision: {prec}, Scale: {scl}")
600
        
601
        elif type_code in STRING:
602
            disp_size = getDisplaySize(type_code, display_size)
603
            if disp_size is not None:
604
                print(f"  Max Length: {disp_size}")
605
        
606
        elif type_code == VerticaType.INTERVAL:
607
            range_str = getIntervalRange(type_code, precision)
608
            leading_prec = getIntervalLeadingPrecision(type_code, precision)
609
            print(f"  Range: {range_str}")
610
            print(f"  Leading Precision: {leading_prec}")
611
        
612
        elif type_code == VerticaType.ARRAY or getComplexElementType(type_code) is not None:
613
            element_type = getComplexElementType(type_code)
614
            if element_type is not None:
615
                element_name = getTypeName(element_type, -1)
616
                type_desc = "Array" if type_code == VerticaType.ARRAY else "Set"
617
                print(f"  {type_desc} of: {element_name}")
618
        
619
        print()
620

621
# Usage
622
with conn.cursor() as cursor:
623
    analyze_table_schema(cursor, 'employees')
624
```