CREATE TYPE
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. |
| 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)