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
NUMERIC / DECIMAL (no precision or scale)	up to 16 bytes	Unconstrained: keeps each value's own display scale, the way PostgreSQL's bare NUMERIC does
BOOLEAN	1 byte
VARCHAR(n) / TEXT	Variable	UTF-8 text; VARCHAR(n) is a distinct declared type from TEXT, with its length enforced
CHAR(n)	Variable	Fixed-width, space-padded; converting to text (::text, CAST ... AS VARCHAR, ||) strips the pad, and casting to CHAR(n) itself truncates or pads without erroring
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, a VARCHAR(n), a CHAR(n), an enum, a domain or a composite type included (VARCHAR(3)[] enforces the length on every element, a mood[] stores labels and orders by the enum); =, <, ORDER BY, and a cast to another array type all compare or convert element by element. An array value has one dimension and a lower bound of 1: a multi-dimensional value ('{{1,2},{3,4}}') or a bounded one ('[0:1]={5,6}') is refused (SQLSTATE 0A000), a known difference from PostgreSQL; a nested ARRAY[[1,2],[3,4]] constructor is stored as its four elements in order and reads back as {1,2,3,4}, where PostgreSQL returns {{1,2},{3,4}}
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 (...))
MONEY	8 bytes	Fixed 2-decimal amount, stored as integer minor units (cents)
INET	Variable	IP host address with an optional netmask; sorts in network order (family, then address, then mask)
CIDR	Variable	IP network address; like INET, but every host bit to the right of the mask must be clear, and the mask always prints
oid	4 bytes	Unsigned object identifier, PostgreSQL system type
xid	4 bytes	Unsigned transaction identifier, PostgreSQL system type
xid8	8 bytes	Unsigned 64-bit transaction identifier, PostgreSQL system type
"char"	1 byte	PostgreSQL's single-byte internal type; must be quoted (bare char means CHAR(n))
name	Variable	PostgreSQL's identifier type, up to 63 bytes
tid	8 bytes	Row address type; not user-declarable, it is the type of the ctid system column
VECTOR(n)	4n + 8 bytes	Dense embedding vector of n 32-bit floats, 1 to 16000 dimensions
HALFVEC(n)	2n + 8 bytes	Dense embedding vector of n 16-bit floats (IEEE 754 binary16), 1 to 16000 dimensions
SPARSEVEC(n)	8*nnz + 16 bytes	Sparse embedding vector declared over 1 to 1,000,000,000 dimensions, storing only its non-zero entries
BIT(n)	n bits	Fixed-length bit string
BIT VARYING(n) / VARBIT	up to n bits	Variable-length bit string, unbounded with no length given

VECTOR, HALFVEC, and SPARSEVEC are ScramDB's native embedding types, compatible with pgvector: the same type names, typmods, text and binary formats, operators, functions, casts, and aggregates. See Vector Types below, and Functions and Expressions for the operators, functions, casts, and aggregates. The JSON-array embeddings_* functions (embeddings_cosine_similarity, embeddings_dot, and the rest) still work over a plain TEXT or JSONB column, for data that is not stored as a native vector.

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
• Bare NUMERIC (no precision or scale given) is exact too, and keeps each value's own display scale the way PostgreSQL does: 1.50 stores and prints as 1.50, not 1.5. Arithmetic follows PostgreSQL's own scale rules (addition and subtraction take the wider input's scale; multiplication adds the two scales; division picks a value-dependent scale, at least 16 significant digits and growing for a smaller quotient). MIN and MAX keep the winning value's own scale (a tie keeps the later row's scale, not the wider one). SUM, AVG, MIN, MAX, and COUNT(DISTINCT) all keep each value's own scale, and that scale survives a sort, a join, a UNION ALL, a window key, and a spill to disk or across the wire (a DISTINCT set operation is the one exception, below); grouping, sorting, and joining key a NUMERIC value by its actual value, never by its raw stored mantissa
• Bare NUMERIC supports NaN, Infinity, and -Infinity, with PostgreSQL's own arithmetic rules for them (for example, Infinity + -Infinity is NaN, and 0 * Infinity is NaN)
• A NUMERIC value is limited to 38 significant digits; a value or a computed result past that limit raises SQLSTATE 22003 ("numeric field overflow") instead of rounding or wrapping
• Text read into a NUMERIC(p,s) (a cast, an INSERT value, COPY) may spell any number of digits, as in PostgreSQL: the digits past the scale round half away from zero, so '1.23456789012345678901234567890123456789012'::numeric(38,2) is 1.23, and only the rounded value has to fit the precision
• A DISTINCT set operation (UNION, INTERSECT, EXCEPT without ALL) over NUMERIC currently prints each value at its trimmed scale (both 1.50 and 1.5 print as 1.5); PostgreSQL keeps the value's original scale there, so this is a known difference from PostgreSQL
• REAL and DOUBLE PRECISION print with the same shortest round-trip digits and fixed/exponential threshold PostgreSQL's own output functions use, so an exact decimal tie prints the same digit PostgreSQL does
• Implicit casting follows PostgreSQL promotion rules (for example, INTEGER widens to BIGINT, then to DOUBLE PRECISION)
• A typed text value (a TEXT column, '5'::text) assigned to a column of another type in INSERT or UPDATE is refused with SQLSTATE 42804 (column "x" is of type integer but expression is of type text), as in PostgreSQL (a JSONB column is the exception, see JSON); an untyped literal ('5') is read by the column type's own input, in VALUES, INSERT ... SELECT and UPDATE ... SET alike (an INET, INTERVAL, TIME, enum label, array text, and a TIMESTAMPTZ read in the session time zone), and so is an untyped literal branch of a CASE, COALESCE, NULLIF, GREATEST or LEAST beside a typed one (see Expressions)
• An equi-join key pair and a comparison compare only types PostgreSQL has an operator for: a TIME joined on an INTEGER is SQLSTATE 42883

String Types

• All text is stored as UTF-8
• VARCHAR(n) enforces its maximum length on INSERT and UPDATE, raising SQLSTATE 22001 on an over-length value; an explicit CAST(x AS VARCHAR(n)) truncates to n characters instead of erroring
• TEXT and VARCHAR without a length are equivalent, and pg_typeof() and the wire type report VARCHAR(n) and unconstrained VARCHAR the same way ("character varying", both OID 1043); only TEXT gets its own type ("text", OID 25). information_schema.columns.character_maximum_length still separates VARCHAR(n) from the other two: it reports the declared width for VARCHAR(n) and NULL for TEXT and unconstrained VARCHAR
• CHAR(n) keeps its blank padding on a plain read, array_to_string(), and to_jsonb(), but converting it to text (::text, CAST(x AS TEXT), CAST(x AS VARCHAR), or ||) strips the padding, matching PostgreSQL
• String comparison is byte-wise, which is the order of every collation that exists: default (the database's C.UTF-8), C and POSIX. COLLATE with any of them is accepted on a column, a domain or a composite field and kept (pg_attribute.attcollation); a collation that orders text by a locale's rules is not available (see CREATE TYPE and CREATE DOMAIN)

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, from 4714-11-24 BC to 294247-01-10 AD; PostgreSQL's range ends in 294276 AD, and a later timestamp is refused (SQLSTATE 22008), a known difference from PostgreSQL
• 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 @>
• A JSONB value a statement computes (->, ||, jsonb_build_object, a ::jsonb or ::json cast) is written to a JSONB column, or a column of a domain over JSONB, by INSERT and UPDATE. A TEXT value written to a JSONB column is read by the JSONB input (SQLSTATE 22P02 when it is not a valid document) where PostgreSQL refuses the assignment with SQLSTATE 42804, a known difference; write ::jsonb to keep such a statement portable
• See Functions for the full set of JSONB operators and functions

Money and Network Types

• MONEY accepts $1,234.56-style text, with or without the $ and grouping commas, and the accounting ($12.34) form for a negative amount. Output is always the $1,234.56 form; a non-C locale format is not implemented
• INET accepts a bare address (192.168.1.1) or address-with-netmask (192.168.1.0/24), IPv4 or IPv6
• CIDR accepts the same forms as INET, but every bit to the right of the mask must be clear: '10.0.0.1/8'::cidr is refused (SQLSTATE 22P02, invalid cidr value, with a DETAIL naming the offending bits), while '10.0.0.0/8'::cidr is accepted. A CIDR value always prints its mask (10.0.0.0/8), where INET omits one that covers the whole address
• All three compare, sort, and hash on their stored representation directly, with no separate collation step

System Types

• oid, xid, and xid8 read and print as plain decimal numbers
• "char" must be written quoted; the bare, unquoted keyword char still means CHAR(n)
• name and "char" are PostgreSQL's own catalog types, useful when matching PostgreSQL system-table shapes

Vector Types

• VECTOR(n) stores n 32-bit floats, n from 1 to 16000; a bare VECTOR with no typmod leaves each row free to hold its own length, the same way an unconstrained NUMERIC keeps each value's own scale. HALFVEC(n) is the same shape at 16-bit float precision (IEEE 754 binary16, magnitude up to 65504). SPARSEVEC(n) declares a dimension from 1 to 1,000,000,000 but stores only its non-zero entries, at most 16000 of them
• Text input for VECTOR and HALFVEC is a bracketed, comma-separated list, '[1,2,3]'. A NaN or infinite element is refused (SQLSTATE 22000, "NaN not allowed in vector" or "infinite value not allowed in vector"); a literal too large to represent (past a finite float4 for VECTOR, past 65504 in magnitude for HALFVEC) is refused (SQLSTATE 22003, naming the offending literal); a value that underflows to zero is stored as 0 (or -0), not an error. A dimension count that does not match the column's typmod is refused (SQLSTATE 22000), naming both counts
• Text input for SPARSEVEC is '{index:value,...}/dimensions', with 1-based indices: '{1:1,3:2.5}/5'. Entries are sorted by index regardless of input order; a duplicate index is refused ("sparsevec indices must not contain duplicates"); an index outside the declared range is refused ("sparsevec index out of bounds"); a zero-valued entry in the text is silently dropped, but an underflow to zero is refused rather than dropped (unlike VECTOR/HALFVEC, where it is silent), and the binary format refuses an explicit zero value outright ("binary representation of sparsevec cannot contain zero values")
• Output for VECTOR and HALFVEC prints each element with the same shortest-round-trip algorithm PostgreSQL's own float4out uses (-0 prints as -0); SPARSEVEC output is the same {index:value,...}/dimensions form, 1-based
• The binary wire format matches pgvector's exactly: VECTOR and HALFVEC send a dimension count and a reserved field, then the elements (32-bit floats for VECTOR, the raw 16-bit half-precision bit pattern for HALFVEC, both network byte order); SPARSEVEC sends the dimension count, the non-zero count, a reserved field, the 0-based indices, then the values
• BIT(n) is a fixed-length bit string of exactly n bits; BIT VARYING(n) (or VARBIT, unbounded with no length given) holds up to n bits. Both use PostgreSQL's own bit-string literal syntax, B'1010'. hamming_distance and jaccard_distance (see Functions) work over two BIT(n) values of matching length; a length mismatch is refused (SQLSTATE 22000), naming both lengths
• VECTOR, HALFVEC, and SPARSEVEC order, hash, and group the same way: value first, element by element, then by dimension count when every compared element is equal (a shorter vector sorts first). ORDER BY, GROUP BY, DISTINCT, and a plain B-tree index over a vector column all use this ordering; the distance operators (see Expressions) are a separate, unrelated ordering by nearness, not by value

CREATE TABLE vector_demo (
    id BIGINT PRIMARY KEY,
    embedding VECTOR(3),
    embedding_half HALFVEC(3),
    embedding_sparse SPARSEVEC(5),
    signature BIT(4)
);

INSERT INTO vector_demo VALUES
    (1, '[1,2,3]', '[1,2,3]', '{1:1,3:2.5}/5', B'1010'),
    (2, '[4,5,6]', '[4,5,6]', '{2:1,5:4}/5', B'0110');

SELECT id, embedding, embedding_half, embedding_sparse, signature
FROM vector_demo
ORDER BY id;

DROP TABLE vector_demo;

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	Rounded half to even, as in PostgreSQL
NUMERIC	Integer	Rounded half away from zero and range-checked, as in PostgreSQL (2.5::numeric::int is 3)
Integer	NUMERIC	Exact, at scale 0
DOUBLE PRECISION	NUMERIC	Rounds through PostgreSQL's 15-significant-digit text form, then reads that text back (0.285::float8::numeric(10,2) is 0.29, not 0.28, because that text form rounds 0.285's exact binary value up first)
REAL	NUMERIC	Same rule, at 6 significant digits
NUMERIC	Float	Converted to the nearest DOUBLE PRECISION or REAL value, as PostgreSQL does
oid, xid, xid8	Integer	Range-checked (oid::bigint, oid::integer)
Boolean	INTEGER	true is 1, false is 0 (the one boolean-to-number cast, as in PostgreSQL; SMALLINT and BIGINT targets are refused)
Integer	Boolean	Nonzero is true
Boolean	String	true / false
String	Boolean	t, true, y, yes, on, 1 and their opposites, case-insensitive, surrounding spaces ignored
String	Number	Parsed
Number	String	Formatted
String	Date/Timestamp	Parsed (ISO 8601); a TIMESTAMPTZ without an offset is read in the session time zone, wherever the text comes from (a literal, a text column, a literal a CASE, COALESCE, ARRAY or UNION gives that type)
String	Array	Read as the array's text, each element by its type's input ('{1,2}'::int[], text_col::int[])
String	INET / CIDR / INTERVAL / TIME	Read by the type's input, a column as well as a literal
Date	Timestamp	At midnight
Timestamp	Date	Time dropped
Array	Array of another element type	Each element cast as a value of its type is (ARRAY[1,2,3]::text[] casts every element to text): a typed element becomes its type's text (ARRAY[time '10:00']::text[] is {10:00:00}), a TIMESTAMP or DATE element becomes a TIMESTAMPTZ in the session time zone and back, and a VARCHAR(n) or CHAR(n) element takes the length (ARRAY['abc']::varchar(2)[] is {ab})