Skip to main content

​
Introduction

Materialize supports a comprehensive set of SQL data types for storing and manipulating data. The type system is PostgreSQL-compatible and includes both built-in types and support for custom types.

​
Built-in Types

​
Numeric Types

​
Integer Types

​
smallint
int2
Small signed integer (2 bytes). Range: -32,768 to 32,767.
SELECT 42::smallint;
​
integer
int, int4
Standard signed integer (4 bytes). Range: -2,147,483,648 to 2,147,483,647.
SELECT 123::integer;
​
bigint
int8
Large signed integer (8 bytes). Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
SELECT 9223372036854775807::bigint;

​
Unsigned Integer Types

​
uint2
Small unsigned integer (2 bytes). Range: 0 to 65,535.
​
uint4
Unsigned integer (4 bytes). Range: 0 to 4,294,967,295.
​
uint8
Large unsigned integer (8 bytes). Range: 0 to 18,446,744,073,709,551,615.

​
Floating-Point Types

​
real
float4
Single precision floating-point (4 bytes). Precision: 6 decimal digits.
SELECT 1.23::real;
​
double precision
float, float8, double
Double precision floating-point (8 bytes). Precision: 15 decimal digits.
SELECT 1.234567890123456::double precision;

​
Exact Numeric Type

​
numeric
decimal
Exact numeric with user-defined precision and scale (16 bytes). Supports arbitrary precision.
SELECT 12345.6789::numeric(10, 4);

​
Text Types

​
text
string
Variable-length Unicode string. No length limit.
SELECT 'Hello, Materialize!'::text;
​
varchar
character varying
Variable-length string with optional length limit.
SELECT 'example'::varchar(100);
​
char
character
Fixed-length string, blank-padded.
SELECT 'abc'::char(5); -- Results in 'abc  '

​
Binary Type

​
bytea
Variable-length binary string. Used for storing raw bytes.
SELECT '\\xDEADBEEF'::bytea;

​
Boolean Type

​
boolean
bool
Logical boolean value: TRUE or FALSE.
SELECT TRUE, FALSE;
SELECT 't'::boolean, 'f'::boolean;

​
Date/Time Types

​
date
Calendar date (year, month, day) without time. 4 bytes.
SELECT DATE '2024-03-15';
SELECT '2024-03-15'::date;
​
time
Time of day (no date, no timezone). 4 bytes.
SELECT TIME '14:30:00';
SELECT '14:30:00'::time;
​
timestamp
Date and time without timezone. 8 bytes.
SELECT TIMESTAMP '2024-03-15 14:30:00';
​
timestamp with time zone
timestamptz
Date and time with timezone. 8 bytes.
SELECT TIMESTAMPTZ '2024-03-15 14:30:00+00';
SELECT '2024-03-15 14:30:00 UTC'::timestamptz;
​
interval
Time span or duration. 32 bytes.
SELECT INTERVAL '1 day 2 hours 30 minutes';
SELECT INTERVAL '1-2 3 4:5:6.7'; -- 1 year, 2 months, 3 days, 4:05:06.7

​
JSON Type

​
jsonb
json
Binary JSON data. Supports indexing and querying JSON documents.
SELECT '{"name": "Alice", "age": 30}'::jsonb;
SELECT data->>'name' FROM json_table;

​
UUID Type

​
uuid
Universally Unique Identifier. 16 bytes.
SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid;

​
OID Type

​
oid
PostgreSQL object identifier. 4 bytes. Used internally for system catalogs.
SELECT 123::oid;

​
Collection Types

​
Arrays

​
array
Multidimensional array of any data type.
SELECT ARRAY[1, 2, 3, 4];
SELECT '{1, 2, 3}'::integer[];
SELECT ARRAY[[1, 2], [3, 4]] AS matrix;

​
Lists

​
list
Variable-length multidimensional list. Similar to arrays but with different semantics.
SELECT LIST[1, 2, 3];
SELECT LIST[[1, 2], [3]];

​
Maps

​
map
Key-value map with text keys and uniform value type.
SELECT '{"a" => 1, "b" => 2}'::map[text=>int];

​
Records

​
record
Tuple with arbitrary contents. Useful for composite types.
SELECT ROW(1, 'text', TRUE);

​
Custom Types

You can create custom types using CREATE TYPE: 
-- Create a custom list type
CREATE TYPE int_list AS LIST (ELEMENT TYPE int4);

-- Create a custom map type
CREATE TYPE string_map AS MAP (KEY TYPE text, VALUE TYPE text);

-- Use the custom type
SELECT '{1, 2, 3}'::int_list;

​
Type Casting

Materialize supports explicit and implicit type casting: 
-- Explicit cast using ::
SELECT '123'::integer;
SELECT 123::text;

-- Explicit cast using CAST()
SELECT CAST('123' AS integer);
SELECT CAST(123 AS text);

-- Implicit casting (automatic)
SELECT 1 + 2.5; -- integer + numeric -> numeric

​
Type Compatibility

When working with different types:
  • Numeric types can generally be cast between each other
  • Text types are interchangeable
  • Date/time types can be cast between compatible types
  • Arrays, lists, and maps require compatible element types
  • Custom types can be cast to their underlying built-in types

​
NULL Values

All types can be nullable unless explicitly marked as NOT NULL: 
CREATE TABLE users (
  id integer NOT NULL,
  email text NOT NULL,
  nickname text  -- nullable
);

​
Type Sizing

Some types have fixed sizes, while others are variable:
Type CategoryStorage
smallint2 bytes
integer4 bytes
bigint8 bytes
real4 bytes
double precision8 bytes
numeric16 bytes
textVariable
byteaVariable
date4 bytes
timestamp8 bytes
uuid16 bytes
jsonbVariable

​
Working with JSON

JSONB supports powerful querying operators: 
-- Extract field
SELECT data->>'name' FROM users;

-- Navigate nested objects
SELECT data->'address'->>'city' FROM users;

-- Check existence
SELECT data ? 'email' FROM users;

-- Array operations
SELECT data->'tags'->0 FROM posts;

Build docs developers (and LLMs) love

Get started for freeTalk to us