Materialize Documentation
s
Join the Community github/materialize

CREATE TYPE

New in v0.6.1.

CREATE TYPE defines a new data type.

Conceptual framework

CREATE TYPE creates custom types, which let you create named versions of anonymous types. For more information, see SQL Data Types: Custom types.

Use

Currently, custom types provide 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.

Syntax

CREATE TYPE type_name AS ( field_name field_type , LIST MAP ( property = val , )
Field Use
type_name A name for the type.
MAP / LIST The data type. If not specified, a row type is assumed.
property = val A property of the new type. This is required when specifying a LIST or MAP type. Note that type properties can only refer to data types within the catalog, i.e. they cannot refer to anonymous list or map types.

row properties

Field Use
field_name The name of a field in a row type.
field_type The data type of a field indicated by field_name.

list properties

Field Use
element_type Creates a custom list whose elements are of element_type.

map properties

Field Use
key_type Creates a custom map whose keys are of key_type. key_type must resolve to text.
value_type Creates a custom map whose values are of value_type.

Details

For details about the custom types CREATE TYPE creates, see SQL Data Types: Custom types.

Properties

All custom type properties' values must refer to named types, e.g. integer.

To create a custom nested list or map, you must first create a custom list or map. This creates a named type, which can then be referred to in another custom type’s properties.

Examples

Custom list

CREATE TYPE int4_list AS LIST (element_type = int4);

SELECT '{1,2}'::int4_list::text AS custom_list;
 custom_list
-------------
 {1,2}

Nested custom list

CREATE TYPE int4_list_list AS LIST (element_type = int4_list);

SELECT '{{1,2}}'::int4_list_list::text AS custom_nested_list;
 custom_nested_list
--------------------
 {{1,2}}

Custom map

CREATE TYPE int4_map AS MAP (key_type=text, value_type=int4);

SELECT '{a=>1}'::int4_map::text AS custom_map;
 custom_map
------------
 {a=>1}

Nested custom map

CREATE TYPE int4_map_map AS MAP (key_type=text, value_type=int4_map);

SELECT '{a=>{a=>1}}'::int4_map_map::text AS custom_nested_map;
 custom_nested_map
-------------------
{a=>{a=>1}}

Custom row type

CREATE TYPE row_type AS (a int, b text);
SELECT ROW(1, 'a')::row_type as custom_row_type;
custom_row_type
-----------------
(1,a)

Nested row type

CREATE TYPE nested_row_type AS (a row_type, b float8);
SELECT ROW(ROW(1, 'a'), 2.3)::nested_row_type AS custom_nested_row_type;
custom_nested_row_type
------------------------
("(1,a)",2.3)
Did this info help?
Yes No