Data Types

Complete guide to TacNode data types including numeric, string, date/time, boolean, binary, array, JSON, UUID, and enum types. Learn type conversion, best practices, and error handling for PostgreSQL-compatible database operations.

TacNode is a PostgreSQL-compatible distributed database that supports a rich set of data types, including basic data types, composite types, and special types, suitable for large-scale data storage and high-concurrency query scenarios.

This document introduces the basic data types and composite data types supported by TacNode, including value range specifications for each type.

Basic Data Types

Numeric Types

String Types

Date and Time Types

Boolean Type

Binary Type

Composite Data Types

Array Types

Supports storing multiple values of the same type, maximum 6 dimensions, single element size limited by TOAST storage (typically 1 GB).

Example:

-- Define array column
CREATE TABLE products (
    id INT,
    tags VARCHAR(20)[] -- String array
);

-- Insert data
INSERT INTO products VALUES (1, ARRAY['electronics', 'gadget']);

-- Query
SELECT tags[1] FROM products WHERE id = 1; -- Returns 'electronics'

JSON / JSONB

Example:

CREATE TABLE user_profiles (
    id INT,
    profile JSONB
);

INSERT INTO user_profiles VALUES (1, '{"name": "Alice", "age": 30}');

-- Query JSON field
SELECT profile->>'name' FROM user_profiles WHERE id = 1; -- Returns "Alice"

Special Data Types

UUID

Stores 128-bit globally unique identifier, format: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.

Example:

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    amount DECIMAL(10,2)
);

Enum Type (ENUM)

Defines fixed-value string enumerations, supports up to 1,000 enumeration values.

Example:

CREATE TYPE status AS ENUM ('pending', 'completed', 'failed');

CREATE TABLE tasks (
    id INT,
    task_status status
);

INSERT INTO tasks VALUES (1, 'pending');

Type Conversion

TacNode supports data type conversion during queries and data operations, including both implicit and explicit conversion methods.

Implicit Type Conversion

In certain operations, TacNode automatically performs type conversion to ensure correct execution. For example, when comparing different numeric types, the system automatically converts them to the same type.

Example:

-- Implicit conversion between INT and BIGINT
SELECT * FROM users WHERE id = 123456789012;
-- id is INT type, but value exceeding range is automatically handled

-- Implicit conversion between string and numeric
SELECT * FROM products WHERE price = '99.99';
-- price is DECIMAL type, string is automatically converted

Explicit Type Conversion

Users can explicitly perform type conversion using the CAST function or :: operator.

Using CAST Function

-- Convert string to integer
SELECT CAST('123' AS INT);

-- Convert numeric to string
SELECT CAST(123.45 AS VARCHAR(10));

-- Convert string to date
SELECT CAST('2023-10-01' AS DATE);

Using :: Operator

-- Convert string to integer
SELECT '123'::INT;

-- Convert numeric to string
SELECT 123.45::VARCHAR(10);

-- Convert string to date
SELECT '2023-10-01'::DATE;

-- Convert timestamp to date
SELECT '2023-10-01 14:30:00'::TIMESTAMP::DATE;

-- Timezone-related conversions
SELECT '2023-10-01 14:30:00+08'::TIMESTAMPTZ; -- Convert to timestamptz
SELECT '2023-10-01 14:30:00'::TIMESTAMP AT TIME ZONE 'Asia/Shanghai'; -- Add timezone info
SELECT '2023-10-01 14:30:00+08'::TIMESTAMPTZ AT TIME ZONE 'UTC'; -- Convert timezone
SELECT '2023-10-01 14:30:00+08'::TIMESTAMPTZ::TIMESTAMP; -- Remove timezone info

Type Conversion Rules

1. Numeric type conversion: Smaller range numeric types can be implicitly converted to larger range numeric types (e.g., INT to BIGINT), but the reverse may require explicit conversion and may lose precision.
2. String and numeric conversion: Strings can be converted to numeric types, provided the string content conforms to numeric format.
3. Date/time conversion: Strings can be converted to date/time types, must conform to standard date/time formats.
4. JSON type conversion: JSON/JSONB types can be converted to and from string types.
5. Timezone conversion: TIMESTAMP and TIMESTAMPTZ can be converted between each other, but timezone information handling must be noted.

Important Notes

• Type conversion may result in data precision loss or conversion failure
• When performing type conversion, ensure source data format is compatible with target type
• Explicit type conversion is safer than implicit conversion; explicit conversion is recommended in critical business logic
• When converting timezones, pay attention to timezone information correctness to avoid time deviation due to improper timezone handling

Handling Conversion Failures

When performing type conversion, if the source data format is incompatible with the target type, conversion will fail and throw an error. Here are common conversion failure scenarios and handling methods:

Common Conversion Failure Scenarios

1. Invalid numeric format conversion

-- Error example: string containing non-numeric characters converted to numeric
SELECT CAST('123abc' AS INT);
-- Error: invalid input syntax for type integer

-- Correct handling: use regex pre-check or exception handling
SELECT CASE WHEN '123abc' ~ '^\d+$' THEN CAST('123abc' AS INT) ELSE NULL END;

2. Out-of-range numeric conversion

-- Error example: numeric value exceeding target type range
SELECT CAST(999999999999 AS SMALLINT);
-- Error: smallint out of range

-- Correct handling: check numeric range or use larger range type
SELECT CASE WHEN 999999999999 BETWEEN -32768 AND 32767 THEN CAST(999999999999 AS SMALLINT) ELSE NULL END;

3. Invalid date/time format conversion

-- Error example: date string not conforming to standard format
SELECT CAST('2023/10/01' AS DATE);
-- Error: invalid input syntax for type date

-- Correct handling: use appropriate date format or preprocess string
SELECT CAST(to_date('2023/10/01', 'YYYY/MM/DD') AS DATE);

4. JSON format error conversion

-- Error example: invalid JSON string converted to JSONB
SELECT CAST('{name: "Alice"}' AS JSONB);
-- Error: invalid input syntax for type json

-- Correct handling: ensure JSON string format is correct
SELECT CAST('{"name": "Alice"}' AS JSONB);

5. Invalid timezone conversion

-- Error example: using non-existent timezone
SELECT '2023-10-01 14:30:00'::TIMESTAMP AT TIME ZONE 'Invalid/Timezone';
-- Error: invalid time zone

-- Error example: incorrect timezone format
SELECT CAST('2023-10-01 14:30:00+25' AS TIMESTAMPTZ);
-- Error: time zone displacement out of range

-- Correct handling: use valid timezone identifiers
SELECT '2023-10-01 14:30:00'::TIMESTAMP AT TIME ZONE 'Asia/Shanghai';
SELECT CAST('2023-10-01 14:30:00+08' AS TIMESTAMPTZ);

Error Handling Recommendations

1. Use conditional expressions for pre-checking: Before performing type conversion, first use conditional expressions to verify if data format meets requirements.

2. Use TRY_CAST function: The TRY_CAST function returns NULL instead of throwing an error when conversion fails.

SELECT TRY_CAST('invalid' AS INT); -- Returns NULL instead of error

3. Use regex for format validation: For string to numeric or date conversions, you can first use regex to validate format.

-- Validate if it's a valid integer format
SELECT CASE WHEN '123abc' ~ '^-?\d+$' THEN CAST('123abc' AS INT) ELSE NULL END;

4. Exception handling: Handle type conversion exceptions at the application level, providing friendly error messages and alternative solutions.