Data Types

Examples on this page use this demo table:

CREATE TABLE products (id BIGINT PRIMARY KEY, name TEXT, price DOUBLE PRECISION, tags TEXT);

ScramDB supports the common PostgreSQL data types, including UUID, JSONB, arrays, and enums. Use the same type names you already use in PostgreSQL.

Supported Types

PostgreSQL Type	Size	Notes
SMALLINT / INT2	2 bytes
INTEGER / INT4	4 bytes
BIGINT / INT8	8 bytes
SERIAL / BIGSERIAL	4 / 8 bytes	Auto-incrementing integer
REAL / FLOAT4	4 bytes	IEEE 754 single precision
DOUBLE PRECISION / FLOAT8	8 bytes	IEEE 754 double precision
NUMERIC(p,s) / DECIMAL(p,s)	up to 16 bytes	Exact decimal, up to 38 digits of precision
BOOLEAN	1 byte
VARCHAR(n) / TEXT	Variable	UTF-8 text
CHAR(n)	Variable	Fixed-width, space-padded
BYTEA	Variable	Raw binary data
UUID	16 bytes
DATE	4 bytes	Calendar date
TIME	8 bytes	Time of day
TIMESTAMP	8 bytes	Date and time, microsecond precision
TIMESTAMPTZ	8 bytes	Timestamp with time zone
INTERVAL	16 bytes	Span of time
JSONB	Variable	Binary JSON with operators and functions
ARRAY (INTEGER[], TEXT[], ...)	Variable	Array of any supported element type
ENUM	-	User-defined enumerated type (CREATE TYPE ... AS ENUM)
DOMAIN	Base type	User-defined constrained type (CREATE DOMAIN)
Composite / ROW(...)	Variable	Row type with named fields (CREATE TYPE ... AS (...))

For vectors and embeddings, store them as JSON arrays in a TEXT column and compare them with the built-in embeddings functions (embeddings_cosine_similarity, embeddings_dot). There is no separate vector column type.

Type Behavior

Numeric Types

• Integer arithmetic is exact and follows standard SQL semantics
• Floating-point arithmetic uses IEEE 754
• Integer overflow is checked at runtime
• NUMERIC(p,s) is exact, with up to 38 digits of precision
• Implicit casting follows PostgreSQL promotion rules (for example, INTEGER widens to BIGINT, then to DOUBLE PRECISION)

String Types

• All text is stored as UTF-8
• VARCHAR(n) enforces its maximum length on insert
• TEXT and VARCHAR without a length are equivalent
• String comparison is currently byte-wise (configurable collations are not available yet)

Date/Time Types

• DATE holds a calendar date, TIME a time of day, and TIMESTAMP / TIMESTAMPTZ a full date and time
• Timestamps have microsecond precision
• EXTRACT() supports YEAR, MONTH, DAY, EPOCH, DOW, DOY, and QUARTER on any date/timestamp source, plus HOUR, MINUTE, and SECOND on a timestamp or time source (not a bare DATE)

JSON

• JSONB stores structured documents in an efficient binary form
• Read fields with ->, ->>, and #>, and test containment with @>
• See Functions for the full set of JSONB operators and functions

NULL Handling

• All types are nullable
• NULL semantics follow the SQL standard (three-valued logic)
• Use IS NULL / IS NOT NULL to test for NULL
• COALESCE() and NULLIF() are supported

Type Casting

Explicit casts use the CAST function or the :: syntax:

SELECT CAST(price AS INTEGER) FROM products;
SELECT price::INTEGER FROM products;

Supported Casts

From	To	Result
Any integer	Any integer	Widened or narrowed
Integer	Float	Exact conversion
Float	Integer	Truncated
String	Number	Parsed
Number	String	Formatted
String	Date/Timestamp	Parsed (ISO 8601)
Date	Timestamp	At midnight
Timestamp	Date	Time dropped