Skip to content

types.md

Latest commit

 

History

History
323 lines (230 loc) · 8.22 KB

types.md

File metadata and controls

323 lines (230 loc) · 8.22 KB
Raw

ClickHouse Types

The library provides PHP classes for ClickHouse-specific types that don't have native PHP equivalents. Use them when inserting data that requires precise type control.

All types implement ClickHouseDB\Type\Type interface and work with insert(), bindings (:param), and native parameters ({name:Type}).

Numeric Types

Integer and floating-point types

The integer types Int8, Int16, Int32, Int64, Int128, Int256 and UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, plus Float32 and Float64, preserve their string representation when used in inserts, bindings, or comparisons:

use ClickHouseDB\Type\UInt128;

$db->insert('table', [[UInt128::fromString('340282366920938463463374607431768211455')]], ['value']);

Float precision (IEEE 754)

Float32, Float64, and BFloat16 are IEEE 754 binary floating-point types (ClickHouse Float docs). Most decimal fractions have no exact binary representation, so arithmetic accumulates rounding errors:

$db->select('SELECT 0.1 + 0.2 AS v')->fetchOne('v');         // 0.30000000000000004 — NOT 0.3
$db->select('SELECT 0.1 + 0.2 = 0.3 AS eq')->fetchOne('eq'); // 0 (false)

Use the Decimal family when you need exact math — decimals store scaled integers and are not affected by IEEE 754 rounding:

$db->select("SELECT toDecimal64('0.1', 1) + toDecimal64('0.2', 1) = toDecimal64('0.3', 1) AS eq")
   ->fetchOne('eq'); // 1 (true)

See tests/Type/Ieee754MantissaTest.php for the full behavior matrix: significand limits (2^24 for Float32, 2^53 for Float64, 2^8 for BFloat16), JSON output rules, and the precision trap when wide decimals are decoded from JSON into PHP floats.

UInt64

Large unsigned integers that overflow PHP's int range.

use ClickHouseDB\Type\UInt64;

$db->insert('table', [
    [UInt64::fromString('18446744073709551615')],
], ['big_number']);

Int64

Large signed integers.

use ClickHouseDB\Type\Int64;

$db->insert('table', [
    [Int64::fromString('-9223372036854775808')],
    [Int64::fromString('9223372036854775807')],
], ['value']);

Decimal

Exact decimal numbers — no floating-point rounding. Unlike the IEEE 754 Float* types, where 0.1 + 0.2 != 0.3, Decimal stores scaled integers and keeps every digit exact.

use ClickHouseDB\Type\Decimal;

$db->insert('table', [
    [Decimal::fromString('12345.6789')],
    [Decimal::fromString('-99999.9999')],
], ['price']);

Decimal32, Decimal64, Decimal128, and Decimal256 are available for explicit ClickHouse decimal column types. ClickHouse Bool values use the existing Boolean class because Bool is a reserved PHP keyword.

Date & Time Types

Date and DateTime

use ClickHouseDB\Type\Date;
use ClickHouseDB\Type\DateTime;

Date::fromString('2024-02-29');
Date::fromDateTime(new DateTimeImmutable('2024-02-29 12:00:00'));
DateTime::fromString('2024-02-29 12:00:00');
DateTime::fromDateTime(new DateTimeImmutable('2024-02-29 12:00:00+00:00'), 'Europe/Amsterdam');

The optional timezone converts PHP date/time objects before formatting, without mutating them. Use the timezone of the destination column (or native parameter). Without it, the object's timezone is preserved. String factories preserve the input exactly; ClickHouse validates date ranges and parses strings in the column's timezone.

DateTime64

Sub-second precision timestamps (milliseconds, microseconds, nanoseconds).

use ClickHouseDB\Type\DateTime64;

// From string
$db->insert('table', [
    [DateTime64::fromString('2024-01-15 10:30:00.123')],
], ['created_at']);

// From PHP DateTimeInterface (precision = 3 for milliseconds)
$dt = new DateTimeImmutable('2024-06-15 12:00:00.456789');
$db->insert('table', [
    [DateTime64::fromDateTime($dt, 3)],  // → '2024-06-15 12:00:00.456'
], ['created_at']);

// Precision options: 0-9 (1=tenths, 3=ms, 6=μs, 9=ns)
DateTime64::fromDateTime($dt, 6);  // → '2024-06-15 12:00:00.456789'
// PHP date/time objects provide at most 6 fractional digits.
// Use fromString() to preserve an existing nanosecond timestamp.

Date32

Extended date range (1900-01-01 to 2299-12-31), unlike Date which is limited to 2149.

use ClickHouseDB\Type\Date32;

// From string
$db->insert('table', [
    [Date32::fromString('2024-01-15')],
], ['event_date']);

// From PHP DateTimeInterface
$db->insert('table', [
    [Date32::fromDateTime(new DateTimeImmutable('2250-12-31'))],
], ['future_date']);

Network Types

IPv4

use ClickHouseDB\Type\IPv4;

$db->insert('table', [
    [IPv4::fromString('192.168.1.1')],
    [IPv4::fromString('10.0.0.1')],
], ['ip']);

IPv6

use ClickHouseDB\Type\IPv6;

$db->insert('table', [
    [IPv6::fromString('::1')],
    [IPv6::fromString('2001:db8::1')],
], ['ip']);

String Types

StringType and FixedString

PHP reserves the name String, so the wrapper is named StringType.

use ClickHouseDB\Type\StringType;
use ClickHouseDB\Type\FixedString;

StringType::fromString("it's a string");
FixedString::fromString('a', 1);  // For a FixedString(1) column.
FixedString::fromString('ab', 2); // For a FixedString(2) column.

FixedString requires a positive length in bytes and a value with exactly that byte length. Both shorter and longer values are rejected. These wrappers retain raw values in getValue(), __toString(), and $value; use bindings or insert() to escape them safely, rather than SQL interpolation.

Enum8 and Enum16

use ClickHouseDB\Type\Enum8;
use ClickHouseDB\Type\Enum16;

Enum8::fromString('active');
Enum16::fromString('pending');

These wrappers represent enum labels. Define the label-to-number mapping in the column or native parameter type, e.g. Enum8('active' = 1, 'inactive' = 2). ClickHouse validates membership and the numeric range of that mapping.

UUID

use ClickHouseDB\Type\UUID;

$uuid = '6d38d288-5b13-4714-b6e4-faa59ffd49d8';

$db->insert('table', [
    [UUID::fromString($uuid)],
], ['id']);

// Select back
$st = $db->select('SELECT id FROM table');
echo $st->fetchOne('id'); // '6d38d288-5b13-4714-b6e4-faa59ffd49d8'

Boolean Type

Boolean

ClickHouse Bool columns. Stored as 1/0 strings internally.

use ClickHouseDB\Type\Boolean;

// From bool
$db->insert('table', [
    [Boolean::fromBool(true)],
    [Boolean::fromBool(false)],
], ['is_active']);

// From string
$db->insert('table', [
    [Boolean::fromString('1')],
], ['is_active']);

Composite Types

MapType

Key-value maps for Map(K, V) columns.

use ClickHouseDB\Type\MapType;

$db->write("CREATE TABLE t (data Map(String, String)) ENGINE = Memory");

$db->insert('t', [
    [MapType::fromArray(['key1' => 'val1', 'key2' => 'val2'])],
], ['data']);

TupleType

Fixed-length tuples for Tuple(T1, T2, ...) columns.

use ClickHouseDB\Type\TupleType;

$db->write("CREATE TABLE t (point Tuple(Float64, Float64)) ENGINE = Memory");

$db->insert('t', [
    [TupleType::fromArray([55.7558, 37.6173])],  // Moscow coordinates
], ['point']);

Using Types with Native Parameters

All types work with selectWithParams() / writeWithParams():

use ClickHouseDB\Type\UUID;
use ClickHouseDB\Type\IPv4;
use ClickHouseDB\Type\DateTime64;

// UUID
$st = $db->selectWithParams(
    'SELECT * FROM users WHERE id = {id:UUID}',
    ['id' => UUID::fromString('6d38d288-5b13-4714-b6e4-faa59ffd49d8')]
);

// IPv4
$st = $db->selectWithParams(
    'SELECT * FROM logs WHERE ip = {ip:IPv4}',
    ['ip' => IPv4::fromString('192.168.1.1')]
);

// DateTime64
$st = $db->selectWithParams(
    'SELECT * FROM events WHERE created_at > {since:DateTime64(3)}',
    ['since' => DateTime64::fromString('2024-01-01 00:00:00.000')]
);

// Array
$st = $db->selectWithParams(
    'SELECT * FROM t WHERE id IN {ids:Array(UInt32)}',
    ['ids' => [1, 2, 3]]
);

Using Types with Bindings

Types also work with the classic :param binding syntax:

use ClickHouseDB\Type\UInt64;

$st = $db->select(
    'SELECT * FROM table WHERE big_id = :id',
    ['id' => UInt64::fromString('18446744073709551615')]
);