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
| 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}}