Skip to Content
SQL ReferenceTypes & Enums

PostgreSQL Data Types in KalamDB

KalamDB uses PostgreSQL-oriented SQL declarations and a shared logical type model for schemas, storage, routine signatures, and client metadata. Use PostgreSQL type names when defining application tables. TypeScript, Dart, and Rust SDKs access those same server columns; they do not define a separate SQL type system.

KalamDB implements these types in its own Rust/DataFusion/Arrow engine. This does not mean every PostgreSQL type, precision rule, or wire representation is identical. FILE and EMBEDDING(n) are KalamDB extensions.

PostgreSQL type names to use

ValueSQL declarationNotes
BooleanBOOLEANBOOL is an alias
Signed integersSMALLINT, INTEGER, BIGINTINT aliases INTEGER
Approximate numbersREAL, DOUBLE PRECISIONExplicit 32-bit / 64-bit choice
Exact decimalsNUMERIC(p,s) or DECIMAL(p,s)Current stored precision is 1–38, with scale from 0 to precision
TextTEXTVARCHAR / CHAR map to text; do not infer PostgreSQL length/padding enforcement
Binary dataBYTEAMaps to the internal Bytes payload; see PGWire limits below
JSON documentsJSON, JSONBDistinct logical types; JSONB canonicalizes document representation
Date / time of dayDATE, TIMEKeep separate from an instant
Wall-clock date and timeTIMESTAMPWithout a time zone
InstantTIMESTAMPTZ or TIMESTAMP WITH TIME ZONEDistinct logical type from TIMESTAMP
UUIDUUIDUUID value rather than an arbitrary text ID
Arrays and named valuesTEXT[], enums, composites, domainsSee named types

Older metadata and SDK descriptors may still say Int, Float, Double, Bytes, DateTime, or Decimal. These internal names are not a recommendation to write a separate KalamDB-specific scalar schema. Prefer INTEGER, REAL, DOUBLE PRECISION, BYTEA, TIMESTAMPTZ, and NUMERIC(p,s) in new SQL.

SQL types versus SDK values

The SQL declaration, the on-disk representation, and the language value returned by an SDK are separate layers. For example, a BIGINT column remains a 64-bit integer even when an HTTP/JSON client represents it as a string to avoid JavaScript precision loss. Live-query rows use the same column types as ordinary queries.

  • TypeScript/React typed tables use drizzle-orm/pg-core builders through @kalamdb/orm. Review the SQL-to-Drizzle mapping and generator limits.
  • Dart and Rust expose typed cell values and schema metadata. Convert with their Dart cell accessors or Rust cell accessors; a wrapper name is not a SQL type declaration.
  • FILE and EMBEDDING(n) require KalamDB-specific handling; importing PostgreSQL Drizzle builders does not turn them into PostgreSQL built-ins.

Current type compatibility limits

The current mapper limits stored decimals to precision 38 and nonnegative scale. It maps FLOAT to the 32-bit internal type; use REAL or DOUBLE PRECISION explicitly rather than assuming PostgreSQL’s FLOAT(p) rules. Unparameterized NUMERIC currently defaults to (38,10) rather than arbitrary precision.

The current PGWire result mapper reports decimal values as TEXT and rejects binary/BYTEA results. Accepting BYTEA in a table definition therefore does not establish a PostgreSQL binary-driver round trip. JSONB and TIMESTAMPTZ have distinct PostgreSQL wire descriptors. These are implementation limits, not alternative SQL names developers need to learn.

Range, multirange, and shell CREATE TYPE forms are rejected. Check the release compatibility boundaries when porting a schema. PostgreSQL’s data type reference describes the upstream types; KalamDB’s supported subset and transport limits are documented here.

Data types are independent from table types, but some types have table-type-specific behavior. FILE columns are supported on USER and SHARED tables, and EMBEDDING(n) columns become most useful with vector search. See /docs/server/architecture/table-types for the table boundary.

Type Selection Flow

Quick Type Selection

Use this table when deciding a column type:

You store…UseWhy
Generated application IDsBIGSERIALFamiliar declaration for a 64-bit identifier
Small whole numbers (status, counters)INTCompact and fast
Large countersBIGINTHigher range
User-facing textTEXTFlexible string storage
True/false valuesBOOLEANClear semantics
Decimal money-like valuesDECIMAL(p,s)Exact precision
TimestampsTIMESTAMPTime-based queries and ordering
Dates onlyDATECalendar values without time
Structured document payloadJSONNested object/array data
Vector embeddingsEMBEDDING(n)Semantic retrieval and similarity search

Common Types in Practice

Integer Types

SQL
CREATE TABLE app.orders (  id BIGSERIAL PRIMARY KEY,  user_id BIGINT NOT NULL,  item_count INT NOT NULL DEFAULT 1) WITH (TYPE = 'USER');
  • Prefer INT for bounded counts.
  • Prefer BIGINT for IDs and values that can grow large.

Text and Boolean

SQL
CREATE TABLE app.users (  id BIGSERIAL PRIMARY KEY,  email TEXT NOT NULL,  display_name TEXT,  is_active BOOLEAN NOT NULL DEFAULT TRUE) WITH (TYPE = 'USER');
  • Use TEXT for variable-length strings.
  • Use BOOLEAN instead of 0/1 integers for feature flags and state.

Decimal Values

SQL
CREATE TABLE billing.invoice_items (  id BIGSERIAL PRIMARY KEY,  sku TEXT NOT NULL,  amount DECIMAL(12,2) NOT NULL,  tax_rate DECIMAL(5,4) NOT NULL DEFAULT 0.0000) WITH (TYPE = 'SHARED');
  • Use DECIMAL for exact arithmetic (pricing, balances, rates).
  • Pick precision/scale deliberately, e.g. DECIMAL(12,2).

Time Types

SQL
CREATE TABLE chat.messages (  id BIGSERIAL PRIMARY KEY,  user_id BIGINT NOT NULL,  content TEXT NOT NULL,  created_at TIMESTAMP NOT NULL DEFAULT NOW()) WITH (TYPE = 'USER'); SELECT *FROM chat.messagesWHERE created_at >= NOW() - INTERVAL '7 days'ORDER BY created_at DESC;
  • Use TIMESTAMP for event records and ordering.
  • Use DATE when time-of-day is not needed.

JSON Payloads

SQL
CREATE TABLE app.events (  id BIGSERIAL PRIMARY KEY,  event_name TEXT NOT NULL,  payload JSON,  created_at TIMESTAMP NOT NULL DEFAULT NOW()) WITH (  TYPE = 'STREAM',  TTL_SECONDS = 300);
  • Use JSON for dynamic payloads and schema-flexible metadata.
  • Keep frequently filtered fields as typed top-level columns.
SQL
CREATE TABLE rag.documents_vectors (  id BIGINT PRIMARY KEY,  doc_embedding EMBEDDING(384)) WITH (TYPE = 'USER'); ALTER TABLE rag.documents_vectors  CREATE INDEX doc_embedding USING COSINE; SELECT idFROM rag.documents_vectorsORDER BY COSINE_DISTANCE(doc_embedding, '[0.12,0.03,0.98]')LIMIT 5;
  • Use EMBEDDING(n) for fixed-size vector columns.
  • Pick n to match the embedding model you generate in your app or worker.
  • Index the embedding column before using it for nearest-neighbor retrieval.

Named types (CREATE TYPE)

Use named composites and enums when several columns or a server function share the same shape. They persist as nested values — not as JSON fallbacks.

SQL
CREATE TYPE app.address AS (  city TEXT,  country TEXT); CREATE TYPE app.message_status AS ENUM ('sent', 'delivered', 'read'); CREATE TABLE app.users (  id TEXT PRIMARY KEY,  name TEXT NOT NULL,  home app.address,  status app.message_status NOT NULL) WITH (TYPE = 'SHARED');

Creating a table also catalogs an implicit row type with the same name (app.users is both the table and the row type). Topics catalog an implicit payload type. Full syntax: CREATE TYPE. Use those names in server function signatures.

Type Conversion

Use explicit casts when moving between compatible types.

SQL
SELECT CAST('42' AS INT) AS count_value;SELECT CAST(amount AS DECIMAL(12,2)) AS normalized_amountFROM billing.invoice_items;

Prefer explicit conversion over relying on implicit casting in critical queries.

Nullability and Defaults

Use NOT NULL for required fields and DEFAULT for stable inserts.

SQL
CREATE TABLE app.sessions (  id BIGSERIAL PRIMARY KEY,  user_id BIGINT NOT NULL,  started_at TIMESTAMP NOT NULL DEFAULT NOW(),  ended_at TIMESTAMP,  is_valid BOOLEAN NOT NULL DEFAULT TRUE) WITH (TYPE = 'USER');
  • IDs: BIGSERIAL PRIMARY KEY
  • Created timestamps: TIMESTAMP NOT NULL DEFAULT NOW()
  • Money/rates: DECIMAL (not floating point)
  • Optional user text: nullable TEXT
  • Real-time transient events: STREAM + TTL_SECONDS

PostgreSQL type distinctions

NUMERIC(p, s) and DECIMAL(p, s) are exact decimal declarations. Choose precision and scale for the stored values; they are not interchangeable with floating point.

JSON and JSONB have distinct logical types. JSONB uses a canonical document representation; do not depend on input key order or whitespace surviving a round trip. TIMESTAMP is a wall-clock value without a time zone; TIMESTAMPTZ represents an instant. Use the latter for timestamps that cross time zones.

Arrays, enums, composites and domains share the logical type catalog used by SQL, storage, routine binding and client metadata. See named types and domains for examples and supported evolution. Custom FILE and EMBEDDING(N) remain KalamDB extensions rather than ordinary PostgreSQL built-ins.

Built-in FunctionsSystem Views