16.1.2 Data Type Dictionary

SQL data types supported by Machbase.

Choose a type for the required value range and precision. Values reserved for NULL, such as an integer type’s minimum or maximum, cannot be ordinary data. The table’s NULL Value column shows internal representations; use SQL NULL to insert and IS NULL to test.

Data Type Summary

Integer Types

SHORT

Signed 16-bit integer. Storage size matches C int16_t, but the minimum value (-32,768) is reserved for NULL. INT16 is also accepted in SQL.

CREATE LOG TABLE t (c1 SHORT);
INSERT INTO t VALUES (-32767);  -- Valid minimum
INSERT INTO t VALUES (-32768);  -- Treated as NULL

USHORT

Unsigned 16-bit integer (uint16_t). The maximum value (65,535) represents NULL.

INTEGER

Signed 32-bit integer. Storage size matches C int32_t, but the minimum is reserved for NULL. INT32 and INT are SQL aliases.

UINTEGER

Unsigned 32-bit integer (uint32_t).

LONG

Signed 64-bit integer. Storage size matches C int64_t, but the minimum is reserved for NULL. INT64 is a SQL alias.

ULONG

Unsigned 64-bit integer (uint64_t).

Floating-point Types

FLOAT

Equivalent to C’s 32-bit float. The maximum positive value represents NULL.

DOUBLE

Equivalent to C’s 64-bit double. The maximum positive value represents NULL.

Fixed-point Types

DECIMAL / NUMERIC

Exact decimal storage within declared precision and scale. Input with more fractional digits than the declared scale may be rounded. Determine required precision before storing amounts or rates. NUMERIC, DEC, FIXED, and NUMBER are aliases for DECIMAL.

CREATE TRANSACTION TABLE invoice (
    id     LONG PRIMARY KEY,
    amount DECIMAL(18,2),
    rate   NUMERIC(7,4)
);

DECIMAL means DECIMAL(10,0); DECIMAL(M) means DECIMAL(M,0). For declarations, rounding, indexes, aggregation, and client mappings, see DECIMAL and NUMERIC Fixed-point Types.

ARRAY Types

Machbase DBMS 8.7.0 supports fixed-length, one-dimensional numeric ARRAYs. Specify cardinality after the element type.

CREATE LOG TABLE sensor_array (
    id INTEGER,
    location DOUBLE[2],
    acceleration FLOAT[3]
);

For element types, NULL distinctions, ingestion/query syntax, and SDK representations, see Numeric ARRAY Types.

Date/Time Types

DATETIME

Internally stores nanoseconds elapsed since midnight on January 1, 1970. Range: 1970-01-01 00:00:00 000:000:000 through 2262-04-11 23:47:16.854:775:807.

• Supports nanosecond precision
• Internal representation: 8-byte integer (nanoseconds since epoch)
• String representation: YYYY-MM-DD HH24:MI:SS mmm:uuu:nnn

-- Convert string to DATETIME
SELECT TO_DATE('2024-01-15 10:30:00 000:000:000');

-- Convert DATETIME to string
SELECT TO_CHAR(ts, 'YYYY-MM-DD HH24:MI:SS') FROM t;

String Types

VARCHAR(n)

Variable-length string. n is the storage limit in bytes, not characters. The LOG declaration range is 1–32,767. UTF-8 characters vary in byte length, so account for encoded size when storing Korean text, emoji, and other multibyte characters.

CREATE LOG TABLE t (name VARCHAR(100), description VARCHAR(1000));

TEXT

Stores large text beyond VARCHAR capacity, up to 64MB. Distinguish text storage support from KEYWORD index support; index availability depends on table type.

• Supported in LOG and Standard Edition TRANSACTION tables
• LOG supports keyword search with KEYWORD indexes and SEARCH
• Not supported in TAG, LOOKUP, or VOLATILE

ORDER BY and GROUP BY cannot operate directly on LOG TEXT columns. This is a query validation constraint, not merely a performance recommendation. Store device IDs, error codes, severity, and other sort/group keys in separate VARCHAR or numeric columns. Using MODIFY COLUMN to change TEXT to VARCHAR is also unsupported.

CREATE LOG TABLE log_table (ts DATETIME, message TEXT);
-- Create a keyword index
CREATE INDEX idx_msg ON log_table (message) INDEX_TYPE KEYWORD;

Binary Types

BINARY

Stores unstructured binary data such as images and documents.

• LOG: Variable length, up to 64MB
• TRANSACTION: Variable-length binary values (Standard Edition)
• TAG: Fixed-length BINARY(n) variant, 1–32,767 bytes
• Not supported in LOOKUP or VOLATILE

TAG BINARY(n):

• Supports X’…’, B’…’, and O’…’ literals, including lowercase prefixes
• Supports ‘0x…’ for compatibility
• Exceeding the declared length causes ERR-02233

Network Address Types

IPV4

Stores IPv4 addresses in 4 bytes, ranging from 0.0.0.0 through 255.255.255.255.

CREATE LOG TABLE access_log (ts DATETIME, src_ip IPV4, dst_ip IPV4);
INSERT INTO access_log VALUES (NOW, '192.168.0.1', '10.0.0.1');
SELECT * FROM access_log WHERE src_ip = TO_IPV4('192.168.0.1');

IPV6

Stores IPv6 addresses in 16 bytes. Abbreviated notation is supported.

• "::FFFF:1232" — Omitted leading zeros
• "::FFFF:192.168.0.3" — IPv4-compatible notation
• "::192.168.3.1" — IPv4-compatible notation (deprecated)

CREATE LOG TABLE v6_log (ts DATETIME, src_ip IPV6);
INSERT INTO v6_log VALUES (NOW, '21DA:D3:0:2F3B:2AA:FF:FE28:9C5A');

JSON Type

Stores JSON documents as text containing key-value pairs.

• Maximum data size: 32,768 bytes
• Maximum JSON path length: 512 bytes
• Supported in TAG, LOG, LOOKUP, and TRANSACTION
• VOLATILE cannot create JSON columns
• LOOKUP JSON columns cannot be primary keys

CREATE LOG TABLE sensor_data (
    ts   DATETIME,
    data JSON
);
INSERT INTO sensor_data VALUES (NOW, '{"temp":23.5,"hum":60}');
SELECT data -> 'temp' AS temperature FROM sensor_data;

For details by table type, see JSON Support by Table Type.

SQL Data Type Mappings

Mappings between Machbase types, SQL standard types, and C types.

Supported Data Types by Table Type

DECIMAL is supported in all public table types. TRANSACTION tables are available in Standard Edition. Cluster Edition supports DECIMAL columns in LOG/TAG tables and DDL propagation.