Data types

Every CamusDB column has a strong type, so each value is stored, compared, and indexed in its native form rather than as loose text. That gives you:

• Exact NUMERIC decimals for money and balances. 0.1 is stored exactly, with no floating-point drift.
• Native UUID and OID keys, stored as compact binary values instead of text.
• ARRAY(T) columns for short lists, such as tags, without a join table.
• JSONB columns for JSON documents, with PostgreSQL operators and GIN indexes.
• BYTES(N) columns that hold vector embeddings for similarity search.

If you know PostgreSQL or MySQL, most type names work as aliases: INTEGER, VARCHAR(N), TEXT, DECIMAL, TIMESTAMP, and BOOLEAN all map to a CamusDB type. You declare a column's type in CREATE TABLE or in ALTER TABLE ... ADD COLUMN. CamusDB then uses that type for storage, comparisons, indexes, casts, defaults, and JSON values.

For application parameters and results, use the .NET, TypeScript, or Python type mappings. Drivers convert application values to the server's transport format; SQL types below describe the values and rules your application uses.

Type reference

SQL type	Stores	Indexable	Notes
OID	A 12-byte ObjectId	Yes	The native identifier type. SQL also accepts OBJECT_ID.
UUID	A 128-bit UUID	Yes	The native UUID or GUID type. CamusDB also accepts GUID.
INT64	A 64-bit signed integer	Yes	Alias: BIGINT. Narrower declarations have their own range limits below.
SMALLINT	A signed 16-bit range, stored as INT64	Yes	Values from -32768 to 32767.
INT, INTEGER	A signed 32-bit range, stored as INT64	Yes	Values from -2147483648 to 2147483647.
SERIAL, BIGSERIAL	A generated INT64 value	Yes	Shorthand for an INT64 identity column backed by an owned sequence.
NUMERIC	An exact decimal number with precision 38 and scale 9	Yes	CamusDB also accepts DECIMAL.
FLOAT64	An IEEE-754 double	Yes	Aliases: FLOAT and DOUBLE PRECISION. CAST also accepts DOUBLE.
FLOAT32	An IEEE-754 single	Yes	CamusDB also accepts REAL. It stores and compares the value at single precision.
BOOL	A boolean	Yes	CamusDB also accepts BOOLEAN.
STRING	Unicode text	Yes	Uses the default string length limit. CamusDB also accepts CHAR, VARCHAR, and TEXT.
STRING(N)	Unicode text, up to N code points	Yes	N must be a positive integer. CHAR(N) and VARCHAR(N) use the same bound.
DATE	A calendar date without a time	Yes	Stored as UTC ticks, truncated to midnight.
DATETIME	An instant in UTC	Yes	CamusDB also accepts TIMESTAMP.
BYTES	An opaque byte string	Yes	CamusDB also accepts BLOB.
BYTES(N)	An opaque byte string, up to N bytes	Yes	N must be a positive integer. BLOB(N) uses the same bound.
ARRAY(T)	An ordered list of scalar values of type T	No	T must be a scalar type. Arrays cannot be used in a primary key or an index.
JSONB	A JSON document, stored in a binary form	GIN only	CamusDB also accepts JSON. A JSONB column cannot be part of a primary key, a UNIQUE constraint, an ordered index, or a foreign key. See JSON documents.

CREATE TABLE events (
  id OID PRIMARY KEY NOT NULL,
  external_id UUID DEFAULT (gen_uuid_v7()),
  name STRING(64) NOT NULL,
  payload BYTES,
  score FLOAT32,
  happened_at DATETIME,
  event_day DATE,
  tags ARRAY(INT64)
);

Type aliases

Alias	Canonical type
BIGINT	INT64
DECIMAL	NUMERIC
FLOAT, DOUBLE PRECISION	FLOAT64
REAL	FLOAT32
TIMESTAMP	DATETIME
BLOB	BYTES
CHAR, VARCHAR, TEXT	STRING
OBJECT_ID	OID
GUID	UUID
BOOLEAN	BOOL
JSON	JSONB

SERIAL and BIGSERIAL are not separate storage types. They are column definition shorthands for an INT64 column backed by an owned sequence, similar to INT64 GENERATED BY DEFAULT AS IDENTITY:

CREATE TABLE invoices (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  invoice_no BIGSERIAL,
  total NUMERIC NOT NULL
);

See Sequences for identity columns, owned sequences, and nextval.

In SQL, an ObjectId column uses OID or OBJECT_ID. In an HTTP table definition, the same type is called id. In SQL, id is still an ordinary column name.

Use a UUID or GUID column for UUID identifiers, not a STRING column. CamusDB stores a native UUID as a compact 128-bit value with a fixed-width, order-preserving index encoding, so it is more efficient than UUID text in memory, on disk, and in indexes.

Integer type names

Choose an integer declaration that matches the values your application stores:

Declaration	Accepted range	Schema output
SMALLINT	-32768 to 32767	SMALLINT
INT, INTEGER	-2147483648 to 2147483647	INT
INT64, BIGINT	-9223372036854775808 to 9223372036854775807	INT64

Writes and casts outside the declared range fail with CADB0420 (HTTP 400, gRPC OUT_OF_RANGE). This covers inserts, updates, upserts, row APIs, defaults, added-column fills, and elements of ARRAY(SMALLINT) or ARRAY(INT).

CREATE TABLE inventory (
  id BIGINT PRIMARY KEY,
  quantity SMALLINT,
  deliveries INT
);
SELECT CAST(32768 AS SMALLINT); -- CADB0420

All integer widths use INT64 storage, arithmetic, indexes, and result values. If quantity holds 32767, selecting quantity + 1 returns 32768; writing it back to that SMALLINT column fails. CREATE TABLE ... AS SELECT produces INT64 integer columns, even from a narrower source. INT16 and INT32 are unsupported.

Existing columns retain their old range. Before this change, INT and SMALLINT were unbounded INT64 aliases. Those columns still accept any INT64 and render as INT64. Old stored views and CHECK bodies retain their normalized INT64 casts. New declarations enforce their widths; use BIGINT or INT64 when values can exceed the 32-bit range.

SERIAL and BIGSERIAL both remain INT64. A sequence attached to a narrow column keeps its own bounds; an out-of-range generated value fails the write.

Integer overflow

INT64 ranges from -9223372036854775808 to 9223372036854775807. Arithmetic, literals, casts, and abs fail with CADB0420 (IntegerValueOutOfRange) when the result is outside that range. Values never wrap around. Use an exact NUMERIC literal for larger numbers:

SELECT 9223372036854775807 + 1; -- CADB0420
SELECT NUMERIC '9223372036854775808';

Integer SUM uses an exact accumulator and checks only the final total, including grouped, window, and distributed aggregates. Intermediate totals can exceed INT64 if the final result fits. AVG divides the exact total and does not overflow an integer accumulator. The remainder of the minimum INT64 divided by -1 is 0; dividing it by -1 raises CADB0420.

An overflow fails the whole statement. Inside an explicit transaction, it also aborts the transaction.

Floating-point values

FLOAT64 and FLOAT32 support infinity and NaN, following PostgreSQL's comparison rules. Use a string conversion to construct them:

SELECT CAST('Infinity' AS FLOAT64), CAST('-Infinity' AS FLOAT64);
SELECT CAST('NaN' AS FLOAT64) = CAST('NaN' AS FLOAT64); -- true

The text is case-insensitive; inf, +inf, and -inf also work. These values can be stored, indexed, grouped, and compared:

• Values sort as -Infinity, finite numbers, Infinity, then NaN.
• All NaNs compare equal, so GROUP BY and DISTINCT combine them.
• Negative zero equals zero. A unique index cannot store both as distinct keys. A covering index read returns 0 for a stored -0.

Finite arithmetic that overflows, or rounds a nonzero result down to zero, fails with CADB0421 (FloatValueOutOfRange). For example, 1e308 * 10 and 1e-300 * 1e-300 fail. Nonzero subnormal values remain valid. The same checks apply to literals, casts, math functions, and narrowing to FLOAT32.

A plain floating-point SUM checks its running total; a window SUM checks the exact frame total. Explicit infinity and NaN inputs follow IEEE arithmetic, but division by zero, sqrt of a negative value, and sign(NaN) fail with CADB0400. Casting infinity or NaN to INT64 raises CADB0420; NUMERIC cannot hold either value.

Use your driver's numeric types and typed parameters. The .NET driver supports native infinity and NaN values. Check the TypeScript and Python mappings before using non-finite parameters; Python currently rejects them.

Non-finite float encoding for client implementers

HTTP JSON represents non-finite floats as strings: "Infinity", "-Infinity", and "NaN". The column type remains FLOAT64 or FLOAT32; finite values, including negative zero, remain JSON numbers. Float parameters accept the same strings, for example {"type": 5, "floatValue": "NaN"}. gRPC carries the native IEEE value in its float or double field.

Existing float indexes: rebuild indexes containing negative zero or NaN that were created before the new encoding. Negative zero now shares zero's key, and all NaNs share a key above infinity. Drop and recreate the affected index; indexes containing neither value need no rebuild. See Drop an index.

Exact decimal numbers

NUMERIC stores exact decimal values with the same range as Spanner GoogleSQL NUMERIC: 38 total digits with 9 digits after the decimal point. The largest value is:

99999999999999999999999999999.999999999

The smallest value is the negative of that number. Values with more than 9 fractional digits round half away from zero:

CREATE TABLE invoices (
  id OID PRIMARY KEY NOT NULL DEFAULT (gen_id()),
  total NUMERIC NOT NULL
);

INSERT INTO invoices (total)
VALUES (0.1), (NUMERIC '12345678901234567890.123456789');

DECIMAL is an alias for NUMERIC. Precision and scale parameters are not supported yet, so NUMERIC(10, 2) and DECIMAL(10, 2) are rejected.

A bare decimal literal still has the ordinary floating-point literal behavior in general expressions. When CamusDB writes one into a NUMERIC column or casts it to NUMERIC, it uses the source text so values such as 0.1 can be stored exactly. Use NUMERIC '...' when you want a typed numeric literal in an expression.

Arithmetic follows these rules:

• NUMERIC with NUMERIC or INT64 returns NUMERIC.
• NUMERIC with FLOAT32 or FLOAT64 returns FLOAT64.
• SUM, AVG, MIN, and MAX over a NUMERIC input return NUMERIC.
• Overflow raises CADB0417 NumericValueOutOfRange.

ARRAY(NUMERIC) is not supported yet.

String and bytes length

STRING(N), CHAR(N), and VARCHAR(N) accept at most N Unicode code points. An emoji such as 🙂 counts as one, so it fits in CHAR(1). A bare STRING column uses the default maximum of 2,621,440 code points.

BYTES(N) accepts at most N bytes. A bare BYTES column uses the default maximum length of 10,485,760 bytes, which is 10 MB.

Both bounds are maximums, not fixed widths, so a BYTES(3072) column accepts shorter values without complaint. Add a CHECK constraint when a column must hold an exact number of bytes. Vector search shows that pattern for embeddings.

CamusDB rejects values that are too long instead of truncating them. An insert, update, or cast that exceeds the column's bound fails with CADB0302 ValueTooLong. A NULL has no length, so CamusDB does not check it against these bounds.

CREATE TABLE documents (
  id OID PRIMARY KEY NOT NULL,
  title STRING(120) NOT NULL,
  body STRING,
  attachment BYTES
);

CHAR(N) uses the same maximum-length rule as VARCHAR: it neither pads nor trims spaces. For example, 'ab' differs from 'ab ', and 'abc ' exceeds CHAR(3). This differs from PostgreSQL CHAR semantics.

Strings use binary UTF-16 code-unit order for comparisons, sorting, MIN/MAX, and indexes. There is no locale-sensitive collation or COLLATE clause. Use LOWER when a comparison should ignore case.

Characters and valid Unicode

Text lengths, substring positions, POSITION/STRPOS, trim character sets, and regex start/result positions count Unicode code points. For example, length('x🙂y') is 3 and substring('x🙂y', 2, 1) is 🙂. Combining marks count separately; the count is not a count of displayed glyphs. String sorting still uses binary UTF-16 order.

A lone half of a UTF-16 surrogate pair is invalid text. SQL string parameters and string-array elements containing one fail with CADB0400 before execution, even when the parameter is unused. The message names the parameter and position. Typed row writes also reject it and identify the column. Previously stored replacement characters (U+FFFD) remain unchanged.

The HTTP SQL API rejects malformed JSON, including escaped surrogate halves. Protobuf clients can replace a surrogate half with U+FFFD before sending gRPC; the server receives valid replacement text and cannot detect that change. Validate text at application boundaries. Regex matching still operates on UTF-16 units and can therefore differ from PostgreSQL for emoji.

Large values

A large STRING, BYTES/BLOB, or ARRAY value can be stored compressed or under its own key outside the row. Each column has a storage strategy: PLAIN, MAIN, EXTERNAL, or the default EXTENDED.

CREATE TABLE files (
  id OID PRIMARY KEY,
  name STRING,
  content BYTES STORAGE EXTERNAL
);

ALTER TABLE files ALTER COLUMN content SET STORAGE PLAIN;

The strategy never changes the value a query returns, only the I/O the query does and the size of each stored row version. See Large Values.

Arrays

ARRAY(T) stores an ordered list of values of one type. The element type must be scalar:

CREATE TABLE measurements (
  id OID PRIMARY KEY NOT NULL,
  samples ARRAY(FLOAT64),
  labels ARRAY(STRING)
);

These rules apply to arrays today:

• CamusDB rejects nested arrays, such as ARRAY(ARRAY(INT64)).
• You cannot use an array column in a primary key or a secondary index.
• SQL supports inline ARRAY[...] literals in expressions and in DML values.
• CamusDB rejects nested array literals, such as ARRAY[ARRAY[1]].
• Array elements can be NULL.

Read one element with a subscript, such as tags[1]. The first element is at index 1, and an index outside the array returns NULL. See PostgreSQL Expression Syntax.

Use Array Functions to read an array as a whole with cardinality, array_length, and array_contains. CamusDB also supports quantified comparisons such as value = ANY(array) and value > ALL(array). See PostgreSQL Expression Syntax.

INSERT INTO measurements (id, samples, labels)
VALUES (GEN_ID(), ARRAY[1.5, 2.0, 2.5], ARRAY['alpha', 'beta']);

UPDATE measurements
SET labels = ARRAY[]
WHERE id = STR_ID('507f1f77bcf86cd799439011');

SQL literal formats

Type	SQL literal form	Example
INT64	An integer	42
NUMERIC	A decimal number or a typed numeric literal	NUMERIC '0.1'
FLOAT64, FLOAT32	A decimal number	3.14
STRING	A quoted string	"hello" or 'hello'
BOOL	true or false	true
OID	A quoted 24-character ObjectId string	"507f1f77bcf86cd799439011"
UUID	A quoted UUID string, with hyphens or as 32 hexadecimal digits	"550e8400-e29b-41d4-a716-446655440000"
DATE	A quoted string in the form yyyy-MM-dd	"2026-03-15"
DATETIME	A quoted ISO-8601 UTC string	"2026-03-15T12:00:00Z"
BYTES	Hexadecimal bytes in the form X'...'	X'DEADBEEF'
ARRAY(T)	ARRAY[...]	ARRAY[1, 2, 3]

Numeric literals use invariant formatting, so the decimal separator is always ., whatever the server's locale.

CamusDB parses date and datetime text as UTC, and returns UUID text in canonical form: lowercase, with hyphens. Invalid date, datetime, UUID, or byte input, or an invalid cast, fails with InvalidInput.

String literals

CamusDB supports two forms of string literal.

A plain string uses single or double quotation marks. It does not treat a backslash as an escape; CamusDB stores it as a normal character. To escape the active quotation mark, repeat it:

SELECT 'plain text';
SELECT 'C:\Users\data';
SELECT 'it''s ready';
SELECT "say ""hello""";

A plain string is the right form for most values, including regular expression patterns and Windows paths:

SELECT name FROM files WHERE path = 'C:\Users\data';
SELECT name FROM users WHERE email ~ '^[^@]+@example\.com$';

An escape string uses the E'...' or E"..." prefix. In that form, a backslash starts an escape sequence:

Escape	Meaning
\\	A backslash
\', \"	A quotation mark
\n, \r, \t, \0, \a, \b, \f, \v	A control character
\NNN	The character with this three-digit octal code
\xHH	The character with this two-digit hexadecimal code
\uHHHH, \UHHHHHHHH	A Unicode code point

COMMENT ON TABLE events IS E'first line\nsecond line';

Use an escape string when the value needs a control character, such as a newline, tab, carriage return, or NUL.

These escapes fail with InvalidInput:

• \x, \u, or \U with too few hexadecimal digits, or with a character that is not a hexadecimal digit.
• \U with a value above U+10FFFF.
• A surrogate (\uD800 to \uDFFF) without its pair.

A malformed octal escape is not an error.

For an escape CamusDB does not recognize, only the escaped character is kept, so E'\d+' stores d+. Use a plain string when you want a literal backslash.

SHOW CREATE TABLE and the other schema renderers emit literals that the parser accepts again. They prefer the plain form and use E'...' only when the value contains a control character.

Bytes literals

Use X'...' for a typed bytes literal:

INSERT INTO documents (id, attachment)
VALUES (GEN_ID(), X'DEADBEEF');

SELECT X'4d5a';
SELECT X''; -- empty byte string

The number of hexadecimal digits must be even. CamusDB also accepts x'...'.

0xFF is an integer literal, not a bytes literal. CamusDB can still coerce string text such as '0xDEADBEEF' to BYTES when it knows the target type, but X'...' carries the bytes type directly and is the preferred SQL literal.

Array literals

Use ARRAY[...] for an inline array value. Each element can be any scalar expression:

SELECT ARRAY[1, 2, 3];
SELECT ARRAY[n::string, upper(name), (n + 1)::string] FROM measurements;

INSERT INTO measurements (id, samples)
VALUES (GEN_ID(), ARRAY[1, 2, 3]);

CamusDB infers the element type from the first non-NULL element. Every other element must have exactly that type or be NULL. Numeric types do not mix: ARRAY[1, 2.5] fails because 1 is INT64 and 2.5 is FLOAT64. Where necessary, CamusDB coerces elements to the target column's declared type.

-- Accepted for ARRAY(FLOAT64): integer elements widen to float64.
INSERT INTO measurements (id, samples)
VALUES (GEN_ID(), ARRAY[1, 2, 3]);

-- Empty arrays adopt the target column's element type.
INSERT INTO measurements (id, samples)
VALUES (GEN_ID(), ARRAY[]);

CamusDB rejects nested array literals.

Casts

CAST(value AS type) works with scalar types. The PostgreSQL shorthand value::type is equivalent:

SELECT
  CAST("2026-03-15" AS DATE) AS event_day,
  CAST("2026-03-15T12:00:00Z" AS DATETIME) AS happened_at,
  CAST("550e8400-e29b-41d4-a716-446655440000" AS UUID) AS external_id,
  CAST("123.45" AS NUMERIC) AS amount,
  CAST(X'DEADBEEF' AS BYTES) AS payload,
  CAST(score AS FLOAT32) AS compact_score
FROM events;

SELECT "42"::INT64 + 1;

See Conversion Functions for the equivalent to_* functions.

Casting floats to integers

CAST, ::, and to_int64 round FLOAT64 and FLOAT32 halfway values away from zero: CAST(2.5 AS INT64) is 3 and CAST(-2.5 AS INT64) is -3. Narrow casts round first, then enforce the target range. PostgreSQL's float-to-bigint cast instead rounds halfway values to the nearest even integer.

NaN, infinity, and out-of-range values fail with CADB0420. Places requiring an integer, including LIMIT, OFFSET, subscripts, and integer columns, still reject a float unless you cast it explicitly.

Stored CHECK expressions use the new rounding for subsequent writes; materialized views keep their existing values until refreshed. Keep nodes on the same version when results must agree during a rolling upgrade.

Comparing different types

CamusDB rejects comparisons without a defined type rule instead of silently returning false. For example, 5 = '5' raises CADB0400 (InvalidInput); write 5 = CAST('5' AS INT64) when conversion is intended.

Types	Comparison rule
Same type	Compare their values.
Numeric types	Compare across numeric types; INT64 with NUMERIC is exact, while a float uses double precision.
DATE and DATETIME	Compare the date at midnight UTC.
String with a temporal value	Parse the string; invalid temporal text is an error.
String with UUID, OID, or JSONB	Parse using that type's comparison rules; unparseable text matches no value.
Arrays	Element types must match, unless one array is empty or contains only untyped NULLs. Numeric array elements do not widen for comparison.
Any other pair	Error naming the operator and both types.

These rules apply to predicates, joins, BETWEEN, IN, quantified comparisons, simple CASE comparisons, NULLIF, and array_contains. A NULL operand yields unknown for valid operand types. Known incompatible types are rejected before reading rows, including on empty input; unknown types are checked when evaluated. An incompatible IN item still fails even if another item matches: 1 IN (1, 'a') is an error.

A new CHECK definition with known incompatible types is rejected at definition time. A type error reached while evaluating an existing check rejects the write with CADB0303.

Errors before reading rows

Mistakes whose types are known from literals, casts, columns, or operators fail even on an empty table, under LIMIT 0 or WHERE FALSE, or in an unused branch. This includes unknown functions (CADB0407), wrong argument counts (CADB0400), incompatible operators/comparisons, and known invalid assignments (CADB0401). Tables with BEFORE triggers defer assignment checks until after the trigger, which may replace the value.

WHERE, HAVING, JOIN ON, and CHECK conditions must be boolean or NULL. WHERE 1 and WHERE 'true' fail with CADB0400; use WHERE TRUE to match all rows deliberately. These checks also cover writes, subqueries, CTAS, views, and new CHECK definitions. Parameters, function argument types, and some derived results still require checks when their values are evaluated.

A division with constant operands and a zero divisor fails with CADB0400 before reading rows: SELECT 1 / 0 WHERE FALSE is an error. A proven unused branch, such as IF(TRUE, 1, 1 / 0), is skipped. A row-dependent condition cannot hide a constant error; use CASE WHEN quantity <> 0 THEN 1 / quantity END to guard a row's divisor. Columns, parameters, function calls (including MOD and %), and subquery results are not constant divisions for this check. Stored view and CHECK definitions defer constant division errors until use; CTAS and materialized-view creation execute the query immediately.

Conditional result types

CASE, COALESCE, IFNULL, NVL, and IF must return one common type across all branches. A branch's value is converted to that type in every row:

Inputs	Common result type
NULL with another type	The other type.
Same type	That type; array element types must also agree, or one array must be empty/untyped.
Numeric types	Their arithmetic result type: INT64 with NUMERIC stays NUMERIC; a float with NUMERIC gives FLOAT64.
DATE with DATETIME	DATETIME.
Quoted string literal with a temporal type	That temporal type; the literal is parsed.
Other combinations	CADB0400; use an explicit CAST.

For example, CASE WHEN enabled THEN 1 ELSE 1.5 END always returns FLOAT64. COALESCE(1, '2') fails: a numeric-looking string does not become a number. A quoted date literal can adopt a temporal type, but a typed string column cannot. Parameters whose types are known only from their values are checked when evaluated. Built-in calls with known argument types, and extension functions with a declared C# return type, contribute their result type even when unused. Thus COALESCE(1, upper('a')) fails, while COALESCE(1, sqrt(4.0)) consistently returns FLOAT64. Calls with parameters or unknown argument types, and JSON_VALUE's document-dependent result, can still require value-time checks.

Known branch types are checked before reading rows, including unused branches and queries over empty tables. CASE and IF select one branch; COALESCE, IFNULL, and NVL stop at the first non-NULL value. See Null functions for examples and IF syntax.

Reserved type keywords

CamusDB reserves these type names and aliases:

oid object_id int int64 integer smallint string char varchar text bool boolean
serial bigserial numeric decimal float float32 float64 real date datetime
timestamp bytes blob uuid guid array

BIGINT and PRECISION remain usable as identifiers despite their type syntax.

Only the exact keyword is reserved. An identifier that merely starts with a type name stays valid, such as internal, dates, or blob_store.