Primitive types

Primitive types store basic values such as numbers, text, booleans, and dates.

Table of Content

• Null values

• Boolean

• VARCHAR(n)

• CHARACTER(n)

• TEXT

• JSON

• Numeric types

---

Null Values

Definition: NULL represents a missing or unknown value.

Notes:

• Not the same as 0, '' (empty string), {} (empty object), [] (empty array), or any zero-equivalent.

• Columns omitted during INSERT get NULL values.

• Outer joins produce NULL for unmatched rows.

• Use NOT NULL to prevent NULL values.

Examples:

-- Create table
CREATE TABLE users (
    first_name TEXT,
    surname TEXT
);

-- Insert with NULL surname
INSERT INTO users (first_name) VALUES ('Alice');

-- Query
SELECT first_name, surname
FROM users
WHERE first_name = 'Alice';
-- Result: Alice | NULL

Preventing NULL values:

CREATE TABLE users_with_surnames (
    first_name TEXT,
    surname TEXT NOT NULL
);

-- This will fail:
INSERT INTO users_with_surnames (first_name) VALUES ('Alice');
-- ERROR: "surname" must not be null

---

Boolean

Definition: Stores a logical value: TRUE or FALSE.

Example:

CREATE TABLE my_table (active BOOLEAN);

INSERT INTO my_table (active) VALUES (true);

SELECT * FROM my_table;
-- Result: TRUE

---

VARCHAR(n)

Definition: Variable-length strings, maximum n characters (1 ≤ n ≤ 2^31 - 1).

Behavior:

• Exceeding n causes an error (unless cast).

• Trailing spaces are truncated.

• Casting truncates without error.

Example:

CREATE TABLE users (
    id VARCHAR,
    name VARCHAR(3)
);

-- Error on length overflow
INSERT INTO users (id, name) VALUES ('1', 'Alice Smith');
-- ERROR: too long for the text type of length: 3

-- Trailing spaces truncated
INSERT INTO users (id, name) VALUES ('1', 'Bob     ');
SELECT id, name, char_length(name) FROM users;
-- Result: 1 | Bob | 3

-- Casting truncates without error
SELECT 'Alice Smith'::VARCHAR(5) AS name;
-- Result: Alice

---

CHARACTER(n)

Definition: Fixed-length strings, blank-padded to n characters.

Behavior:

• Over-length values cause an error (unless cast).

• Shorter values are right-padded with spaces.

• Casting truncates without error.

Example:

CREATE TABLE users (
    id CHARACTER,
    name CHAR(3)
);

-- Right-padding
INSERT INTO users (id, name) VALUES ('1', 'Bo');
SELECT id, name, char_length(name) FROM users;
-- Result: 1 | Bo  | 3

---

TEXT

Definition: Unbounded variable-length Unicode text.

Notes:

• Max indexed length: 32,766 bytes (UTF-8) for column store.

• Use OBJECT for structured JSON-like data.

Example:

CREATE TABLE users (name TEXT);

INSERT INTO users (name) VALUES ('🌻 Alice 🌻');

SELECT * FROM users;
-- Result: 🌻 Alice 🌻

---

json

Definition: PostgreSQL-compatible JSON type (only for casting, not table columns).

Examples:

-- String to JSON
SELECT '{"x": 10}'::json;
-- Result: {"x": 10}

-- JSON to OBJECT
SELECT ('{"x": 10}'::json)::object;
-- Result: {"x": 10}

-- OBJECT to JSON
SELECT {x=10}::json;
-- Result: {"x":10}

---

Numeric types

CrateDB supports:

• SMALLINT

• INTEGER

• BIGINT

• NUMERIC(precision, scale)

• REAL

• DOUBLE PRECISION

Notes:

• REAL and DOUBLE PRECISION are inexact (floating-point).

• Aggregates (SUM, AVG) on floats may vary slightly due to rounding.

• Supports IEEE 754 special values: NaN, Infinity, -Infinity, -0.

Example:

SELECT
    0.0 / 0.0 AS a,    -- NaN
    1.0 / 0.0 AS b,    -- Infinity
    1.0 / -0.0 AS c;   -- -Infinity

Inserting special values:

CREATE TABLE my_table (
    col_dp DOUBLE PRECISION,
    col_r REAL
);

INSERT INTO my_table (col_dp, col_r)
VALUES ('NaN', 'Infinity');

SELECT col_dp, col_r FROM my_table;
-- Result: NaN | Infinity