Materialize Documentation
s
Join the Community github/materialize

SQL data types

Materialize’s type system consists of two classes of types:

Built-in types

Type Aliases Use Size (bytes) Catalog name Syntax
bigint int8 Large signed integer 8 Named 123
boolean bool State of TRUE or FALSE 1 Named TRUE, FALSE
bytea bytea Unicode string Variable Named '\xDEADBEEF' or '\\000'
date Date without a specified time 4 Named DATE '2007-02-01'
double precision float, float8, double Double precision floating-point number 8 Named 1.23
integer int, int4 Signed integer 4 Named 123
interval Duration of time 32 Named INTERVAL '1-2 3 4:5:6.7'
jsonb json JSON Variable Named '{"1":2,"3":4}'::jsonb
map Map with text keys and a uniform value type Variable Anonymous '{a => 1, b => 2}'::map[text=>int]
list Multidimensional list Variable Anonymous LIST[[1,2],[3]]
numeric decimal Signed exact number with user-defined precision and scale 16 Named 1.23
oid PostgreSQL object identifier 4 Named 123
real float4 Single precision floating-point number 4 Named 1.23
record Tuple with arbitrary contents Variable Unnameable ROW($expr, ...)
smallint int2 Small signed integer 2 Named 123
text string Unicode string Variable Named 'foo'
time Time without date 4 Named TIME '01:23:45'
timestamp Date and time 8 Named TIMESTAMP '2007-02-01 15:04:05'
timestamp with time zone timestamp with time zone Date and time with timezone 8 Named TIMESTAMPTZ '2007-02-01 15:04:05+06'
Arrays ([]) Multidimensional array Variable Named ARRAY[...]

Catalog name

Value Description
Named Named types can be referred to using a qualified object name, i.e. they are objects within the pg_catalog schema. Each named type a unique OID.
Anonymous Anonymous types cannot be referred to using a qualified object name, i.e. they do not exist as objects anywhere.

Anonymous types do not have unique OIDs for all of their possible permutations, e.g. int4 list, float8 list, and date list list share the same OID.

You can create named versions of some anonymous types using custom types.
Unnameable Unnameable types are anonymous and do not yet support being custom types.

Custom types

Custom types, in general, provide a mechanism to create names for specific instances of anonymous types to suit users' needs.

However, types are considered custom if the type:

To create custom types, see CREATE TYPE.

Use

Currently, custom types only provides a shorthand for referring to otherwise-annoying-to-type names, but in the future will provide binary encoding and decoding for these types, as well.

Casts

Structurally equivalent types can be cast to and from one another; the required context depends on the types themselves, though.

From To Cast permitted
Custom type Built-in type Implicitly
Built-in type Custom type Implicitly
Custom type 1 Custom type 2 For explicit casts

Equality

Values in custom types are never considered equal to:

Polymorphism

When using custom types as values for polymorphic functions, the following additional constraints apply:

Examples

This is a little easier to understand if we make it concrete, so we’ll focus on concatenating two lists and appending an element to list.

For these operations, Materialize uses the following polymorphic parameters:

When concatenating two lists, we’ll use list_cat whose signature is list_cat(l: listany, r: listany).

If we concatenate a custom list (in this example, custom_list) and a structurally equivalent built-in list (int4 list), the result is of the same type as the custom list (custom_list).

CREATE TYPE custom_list AS LIST (element_type=int4);

SELECT pg_typeof(
  list_cat('{1}'::custom_list, '{2}'::int4 list)
) AS custom_list_built_in_list_cat;

 custom_list_built_in_list_cat
-------------------------------
 custom_list

When appending an element to a list, we’ll use list_append whose signature is list_append(l: listany, e: listelementany).

If we append a structurally appropriate element (int4) to a custom list (custom_list), the result is of the same type as the custom list (custom_list).

SELECT pg_typeof(
  list_append('{1}'::custom_list, 2)
) AS custom_list_built_in_element_cat;

 custom_list_built_in_element_cat
----------------------------------
 custom_list

If we append a structurally appropriate custom element (custom_list) to a built-in list (int4 list list), the result is a list of custom elements.

SELECT pg_typeof(
  list_append('{{1}}'::int4 list list, '{2}'::custom_list)
) AS built_in_list_custom_element_append;

 built_in_list_custom_element_append
-------------------------------------
 custom_list list

This is the “least custom type” we could support for these values––i.e. Materialize will not create or discover a custom type whose elements are custom_list, nor will it coerce custom_list into an anonymous built-in list.

Note that custom_list list is considered a custom type because it contains a reference to a custom type. Because it’s a custom type, it enforces custom types' polymorphic constraints.

For example, values of type custom_list list and custom_nested_list cannot both be used as listany values for the same function:

CREATE TYPE custom_nested_list AS LIST (element_type=custom_list);

SELECT list_cat(
  -- result is "custom_list list"
  list_append('{{1}}'::int4 list list, '{2}'::custom_list),
  -- result is custom_nested_list
  '{{3}}'::custom_nested_list
);
ERROR: Cannot call function list_cat(custom_list list, custom_nested_list)...

As another example, when using custom_list list values for listany parameters, you can only use custom_list or int4 list values for listelementany parameters––using any other custom type will fail:

CREATE TYPE second_custom_list AS LIST (element_type=int4);

SELECT list_append(
  -- elements are custom_list
  '{{1}}'::custom_nested_list,
  -- second_custom_list is not interoperable with custom_list because both
  -- are custom
  '{2}'::second_custom_list
);
ERROR:  Cannot call function list_append(custom_nested_list, second_custom_list)...

To make custom types interoperable, you must cast them to the same type. For example, casting custom_nested_list to custom_list list (or vice versa) makes the values passed to listany parameters of the same custom type:

SELECT pg_typeof(
  list_cat(
    -- result is "custom_list list"
    list_append(
      '{{1}}'::int4 list list,
      '{2}'::custom_list
    ),
    -- result is "custom_list list"
    '{{3}}'::custom_nested_list::custom_list list
  )
) AS complex_list_cat;
 complex_list_cat
------------------
 custom_list list