Materialize Documentation
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 LIST MAP ( field = val , )
Field Use
type_name A name for the type.
field = val A property of the new 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.

list properties

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

map properties

Name Use
key_type Creates a custom map whose keys are are of key_type. key_type must resolve to text.
value_type Creates a custom map whose values are 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 lists

CREATE TYPE int4_list AS LIST (element_type = int4);

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

Nested custom lists

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 maps

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 maps

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}}
Did this info help?
Yes No