Data Types

ParticleDB stores data in Apache Arrow columnar format. Every value belongs to one of the types below. Type names are case-insensitive in SQL.

Numeric Types

INT64

64-bit signed integer. This is the default integer type and covers the full range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Aliases: INT, BIGINT, INTEGER, SMALLINT, TINYINT

All integer aliases map to the same underlying 64-bit storage. ParticleDB does not enforce smaller ranges for SMALLINT or TINYINT — they exist for migration compatibility with other databases.

CREATE TABLE sensors (
    id          BIGINT PRIMARY KEY,
    reading     INT64 NOT NULL,
    retry_count SMALLINT DEFAULT 0
);

INSERT INTO sensors (id, reading) VALUES (1, 42);
INSERT INTO sensors (id, reading) VALUES (2, -9223372036854775808);  -- min value

FLOAT64

64-bit IEEE 754 double-precision floating point. Supports special values NaN, Infinity, and -Infinity.

Aliases: FLOAT, DOUBLE, REAL, DOUBLE PRECISION

CREATE TABLE measurements (
    id    BIGINT PRIMARY KEY,
    temp  DOUBLE PRECISION,
    error FLOAT64
);

INSERT INTO measurements (id, temp, error) VALUES (1, 98.6, 0.001);
SELECT ROUND(temp, 1) FROM measurements;  -- 98.6

---

Boolean

BOOLEAN

Stores TRUE, FALSE, or NULL.

Aliases: BOOL

CREATE TABLE feature_flags (
    name    TEXT PRIMARY KEY,
    enabled BOOLEAN NOT NULL DEFAULT FALSE
);

INSERT INTO feature_flags VALUES ('dark_mode', TRUE);
SELECT * FROM feature_flags WHERE enabled;

---

String Types

TEXT

Variable-length UTF-8 string with no maximum length constraint.

Aliases: VARCHAR, STRING, CHAR, VARCHAR(n)

Length parameters like VARCHAR(255) are accepted for compatibility but not enforced. All string types use the same underlying storage.

CREATE TABLE users (
    id       BIGINT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    bio      TEXT
);

INSERT INTO users VALUES (1, 'alice', 'Software engineer from Portland');
SELECT LENGTH(bio), UPPER(username) FROM users;

---

Binary Types

BYTES

Variable-length byte array for storing raw binary data.

Aliases: BYTEA, BLOB, BINARY

Use hex literals with X'' syntax or byte-escape strings:

CREATE TABLE files (
    id      BIGINT PRIMARY KEY,
    name    TEXT NOT NULL,
    content BYTES
);

INSERT INTO files VALUES (1, 'icon.png', X'89504E470D0A1A0A');

---

Date / Time

TIMESTAMP

Stores a point in time as microseconds since the Unix epoch (1970-01-01 00:00:00 UTC). Provides microsecond precision.

CREATE TABLE events (
    id         BIGINT PRIMARY KEY,
    name       TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP
);

INSERT INTO events (id, name) VALUES (1, 'signup');

SELECT
    name,
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month
FROM events;

Use NOW() or CURRENT_TIMESTAMP to get the current time. Use EXTRACT() to pull out components and DATE_TRUNC() to round down to a given precision:

-- Truncate to the start of the hour
SELECT DATE_TRUNC('hour', created_at) AS hour, COUNT(*)
FROM events
GROUP BY DATE_TRUNC('hour', created_at)
ORDER BY hour;

For time-series bucketing, use TIME_BUCKET:

-- 5-minute buckets
SELECT TIME_BUCKET(300, event_time) AS bucket, AVG(value)
FROM metrics
GROUP BY bucket
ORDER BY bucket;

---

Vector Type

VECTOR(n)

Dense array of n 32-bit floating-point values, designed for embedding vectors and similarity search. The dimension n is specified at column creation time.

CREATE TABLE documents (
    id        BIGINT PRIMARY KEY,
    title     TEXT NOT NULL,
    embedding VECTOR(384)
);

INSERT INTO documents (id, title, embedding)
VALUES (1, 'Introduction to ML', '[0.1, 0.2, -0.05, ...]');

Vector literals are written as JSON-style arrays in a string: '[0.1, 0.2, 0.3]'.

Use distance functions for similarity search:

-- Find 10 nearest neighbors by Euclidean distance
SELECT id, title, L2_DISTANCE(embedding, '[0.1, 0.2, ...]') AS distance
FROM documents
ORDER BY distance
LIMIT 10;

-- Cosine similarity (higher = more similar)
SELECT id, title, COSINE_SIMILARITY(embedding, '[0.1, 0.2, ...]') AS sim
FROM documents
ORDER BY sim DESC
LIMIT 10;

Create an HNSW or IVF index for fast approximate nearest-neighbor search. See Vector Search for full details.

---

JSON

JSON / JSONB

Stores a JSON document as text. Both JSON and JSONB are accepted as type names and map to the same storage.

CREATE TABLE api_logs (
    id       BIGINT PRIMARY KEY,
    payload  JSON NOT NULL,
    metadata JSONB
);

INSERT INTO api_logs VALUES (
    1,
    '{"method": "POST", "path": "/users", "status": 201}',
    '{"region": "us-east-1"}'
);

Access JSON fields using the -> and ->> operators:

-- -> returns a JSON element
SELECT payload -> 'method' FROM api_logs;

-- ->> returns the value as text
SELECT payload ->> 'status' FROM api_logs WHERE payload ->> 'method' = 'POST';

These operators map to JSON_EXTRACT and JSON_EXTRACT_SCALAR respectively.

---

Array

ARRAY

Stores an ordered list of scalar values. Array literals use the ARRAY[...] syntax:

CREATE TABLE surveys (
    id      BIGINT PRIMARY KEY,
    tags    ARRAY,
    scores  ARRAY
);

INSERT INTO surveys VALUES (1, ARRAY['quality', 'speed', 'cost'], ARRAY[9, 8, 7]);

Use UNNEST to expand an array into rows:

SELECT tag FROM UNNEST(ARRAY['a', 'b', 'c']) AS t(tag);
-- Returns 3 rows: a, b, c

---

Enum Types

ENUM (via CREATE TYPE)

User-defined enumerated types restrict a column to a fixed set of string values. Values are validated on INSERT and UPDATE.

-- Define the type
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- Use it in a table
CREATE TABLE orders (
    id     BIGINT PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending'
);

INSERT INTO orders VALUES (1, 'pending');    -- OK
INSERT INTO orders VALUES (2, 'invalid');    -- ERROR: invalid enum value

-- Clean up
DROP TYPE order_status;

---

Type Casting

ParticleDB supports three cast syntaxes for converting between types.

CAST

Standard SQL syntax. Raises an error if the conversion fails:

SELECT CAST('42' AS INT64);          -- 42
SELECT CAST(3.14 AS INT64);          -- 3
SELECT CAST(1 AS BOOLEAN);           -- true
SELECT CAST('2025-01-01' AS TEXT);   -- '2025-01-01'

TRY_CAST

Returns NULL instead of raising an error on conversion failure:

SELECT TRY_CAST('hello' AS INT64);   -- NULL (no error)
SELECT TRY_CAST('42' AS INT64);      -- 42
SELECT TRY_CAST(NULL AS FLOAT64);    -- NULL

:: Operator

PostgreSQL-style shorthand for CAST:

SELECT '42'::INT64;           -- 42
SELECT 3.14::INT64;           -- 3
SELECT '2025-03-28'::TEXT;    -- '2025-03-28'

---

Implicit Type Coercion

ParticleDB automatically promotes types in certain contexts:

• INT64 + FLOAT64 arithmetic promotes to FLOAT64
• INT64 compared to FLOAT64 promotes the integer to float
• BOOL can be coerced to INT64 (TRUE = 1, FALSE = 0)

---

NULL Handling

All types are nullable by default. Use NOT NULL in column definitions to disallow null values:

CREATE TABLE products (
    id    BIGINT PRIMARY KEY,           -- implicitly NOT NULL
    name  TEXT NOT NULL,                 -- explicitly NOT NULL
    notes TEXT                           -- nullable (default)
);

NULL follows standard SQL three-valued logic:

• NULL = NULL evaluates to NULL (not TRUE)
• Use IS NULL / IS NOT NULL to test for null
• COALESCE(a, b, c) returns the first non-null argument
• NULLIF(a, b) returns NULL when a = b