# SQL data types

Learn more about the SQL data types supported in Materialize




> For the complete documentation index, see [llms.txt](https://preview.materialize.com/materialize/40269/llms.txt).


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

- [Built-in types](#built-in-types)
- [Custom types](#custom-types) created through [`CREATE TYPE`][create-type]

## Built-in types

The following table lists Materialize's built-in types and details. In the
table, the logical width is the nominal width of the type (similar to
PostgreSQL's `pg_type.typlen`). The actual number of bytes written depends on
the actual value. For example, a `bigint` value of `3` takes 2 bytes total (1
payload byte plus 1 tag byte).

| Type | Aliases | Description | Logical width (bytes) | Catalog name | Example |
| -----|-------|-----|--------------|----------------|-----|
[`bigint`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/integer/index.md) | `int8` | Large signed integer | 8 | Named | `123`
[`boolean`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/boolean/index.md) | `bool` | State of `TRUE` or `FALSE` | 1 | Named | `TRUE`, `FALSE`
[`bytea`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/bytea/index.md) | | Binary data | Variable | Named | `'\xDEADBEEF'` or `'\\000'`
[`date`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/date/index.md) | | Date without a specified time | 4 | Named | `DATE '2007-02-01'`
[`double precision`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/float/index.md) | `float`, `float8`, `double` | Double precision floating-point number | 8 | Named | `1.23`
[`integer`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/integer/index.md) | `int`, `int4` | Signed integer | 4 | Named | `123`
[`interval`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/interval/index.md) | | Duration of time | 16 | Named | `INTERVAL '1-2 3 4:5:6.7'`
[`jsonb`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md) | `json` | JSON | Variable | Named | `'{"1":2,"3":4}'::jsonb`
[`map`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/map/index.md) | | Map with [`text`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/text/index.md) keys and a uniform value type | Variable | Anonymous | `'{a => 1, b => 2}'::map[text=>int]`
[`list`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/list/index.md) | | Multidimensional list | Variable | Anonymous | `LIST[[1,2],[3]]`
[`numeric`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/numeric/index.md) | `decimal` | Signed exact number with user-defined precision and scale | 16 | Named | `1.23`
[`oid`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/oid/index.md) | | PostgreSQL object identifier | 4 | Named | `123`
[`real`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/float/index.md) | `float4` | Single precision floating-point number | 4 | Named | `1.23`
[`record`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/record/index.md) | | Tuple with arbitrary contents | Variable | Unnameable | `ROW($expr, ...)`
[`smallint`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/integer/index.md) | `int2` | Small signed integer | 2 | Named | `123`
[`text`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/text/index.md) | `string` | Unicode string | Variable | Named | `'foo'`
[`time`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/time/index.md) | | Time without date | 8 | Named | `TIME '01:23:45'`
[`uint2`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/uint/index.md) | | Small unsigned integer | 2 | Named | `123`
[`uint4`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/uint/index.md) | | Unsigned integer | 4 | Named | `123`
[`uint8`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/uint/index.md) | | Large unsigned integer | 8 | Named | `123`
[`timestamp`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/timestamp/index.md) | | Date and time | 8 | Named | `TIMESTAMP '2007-02-01 15:04:05'`
[`timestamp with time zone`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/timestamp/index.md) | `timestamptz` | Date and time with timezone | 8 | Named | `TIMESTAMPTZ '2007-02-01 15:04:05+06'`
[Arrays](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/array/index.md) (`[]`) | | Multidimensional array | Variable | Named | `ARRAY[...]`
[`uuid`](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/uuid/index.md) | | UUID | 16 | Named | `UUID 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'`

#### 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.<br/><br/>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.<br/><br/>You can create named versions of some anonymous types using [custom types](#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:
- Was created through [`CREATE TYPE`][create-type].
- Contains a reference to a custom type.

To create custom types, see [`CREATE TYPE`][create-type].

### Use

Currently, custom types only provides a shorthand for referring to
otherwise-annoying-to-type names.

### 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](https://preview.materialize.com/materialize/40269/markdown-docs/sql/functions/cast/index.md)

### Equality

Values in custom types are _never_ considered equal to:

- Other custom types, irrespective of their structure or value.
- Built-in types, but built-in types can be coerced to and from structurally
  equivalent custom types.

### Polymorphism

When using custom types as values for [polymorphic
functions](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/list/index.md#polymorphism), the following additional constraints apply:

- If any value passed to a polymorphic parameter is a custom type, the resultant
  type must use the custom type in the appropriate location.

  For example, if a custom type is used as:
  - `listany`, the resultant `list` must be of exactly the same type.
  - `listelementany`, the resultant `list`'s element must be of the custom type.
- If custom types and built-in types are both used, the resultant type is the
  "least custom type" that can be derived––i.e. the resultant type will have the
  fewest possible layers of custom types that still fulfill all constraints.
  Materialize will neither create nor discover a custom type that fills the
  constraints, nor will it coerce a custom type to a built-in type.

  For example, if appending a custom `list` to a built-in `list list`, the
  resultant type will be a `list` of custom `list`s.

#### 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:

- `listany`, which accepts any `list`, and constrains all lists to being of the
  same structurally equivalent type.
- `listelementany`, which accepts any type, but must be equal to the element
  type of the `list` type used with `listany`. For instance, if `listany` is
  constrained to being `int4 list`, `listelementany` must be `int4`.

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`).

```mzsql
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;

```
```nofmt
 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`).

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

```
```nofmt
 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.

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

```
```nofmt
 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:

```mzsql
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
);
```
```nofmt
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:

```mzsql
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
);
```
```nofmt
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:

```mzsql
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;
```
```nofmt
 complex_list_cat
------------------
 custom_list list
```

[create-type]: https://preview.materialize.com/materialize/40269/markdown-docs/sql/create-type/index.md


## Pages in this section

- [Array types](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/array/index.md): Express sequences of other types
- [boolean type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/boolean/index.md): Expresses TRUE or FALSE
- [bytea type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/bytea/index.md): Expresses a binary string
- [date type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/date/index.md): Expresses a date without a specified time
- [Floating-point types](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/float/index.md): Express signed, inexact numbers
- [Integer types](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/integer/index.md): Express signed integers
- [interval type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/interval/index.md): Expresses a duration of time
- [jsonb type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/jsonb/index.md): Expresses a JSON object
- [List types](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/list/index.md): Lists are ordered sequences of homogenously typed elements
- [map type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/map/index.md): Expresses a map
- [mz_aclitem type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/mz_aclitem/index.md): Expresses a granted privilege on some object
- [mz_timestamp type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/mz_timestamp/index.md): Expresses an internal timestamp
- [numeric type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/numeric/index.md): Expresses an exact number with user-defined precision and scale
- [oid type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/oid/index.md): Express a PostgreSQL-compatible object identifier
- [record type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/record/index.md): A tuple with arbitrary contents
- [text type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/text/index.md): Expresses a Unicode string
- [time type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/time/index.md): Expresses a time without a specific date
- [Timestamp types](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/timestamp/index.md): Expresses a date and time
- [Unsigned Integer types](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/uint/index.md): Express unsigned integers
- [uuid type](https://preview.materialize.com/materialize/40269/markdown-docs/sql/types/uuid/index.md): Expresses a universally-unique identifier