CREATE TYPE

The CREATE TYPE statement creates a new type in a database. After the type is created, it can only be referenced from the database that contains the type. The following user-defined data types can be created with this statement:

Enumerated data type
Composite data type

The CREATE TYPE statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Synopsis

Parameters

Parameter	Description
`type_name`	The name of the type. You can qualify the name with a database and schema name (e.g., `db.typename`), but after the type is created, it can only be referenced from the database that contains the type.
`IF NOT EXISTS`	Create a new type only if a type of the same name does not already exist in the database; if one does exist, do not return an error.
`opt_enum_val_list`	A list of values that make up the type’s enumerated set.
`opt_composite_type_list`	A list of values that make up the set of types that make up a composite type.

Required privileges

To create a type, the user must have the CREATE privilege on the parent database and the schema in which the type is being created.
To use a user-defined type in a table (e.g., when defining a column’s type), the user must have the USAGE privilege on the type.

Example

Create an enumerated data type

Use the statements below to create an ENUM data type.

> CREATE TYPE IF NOT EXISTS status AS ENUM ('open', 'closed', 'inactive');

To see all user-defined data types, use SHOW TYPES:

> SHOW TYPES;

  schema |  name  | owner
---------+--------+--------
  public | status | root
(1 row)

To see the values accepted by the underlying ENUM data type, use SHOW ENUMS:

> SHOW ENUMS;

  schema |  name  |         values         | owner
---------+--------+------------------------+--------
  public | status | {open,closed,inactive} | root
(1 row)

> CREATE TABLE IF NOT EXISTS accounts (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        balance DECIMAL,
        status status
);

> INSERT INTO accounts(balance,status) VALUES (500.50,'open'), (0.00,'closed'), (1.25,'inactive');

> SELECT * FROM accounts;

                   id                  | balance |  status
---------------------------------------+---------+-----------
  3848e36d-ebd4-44c6-8925-8bf24bba957e |  500.50 | open
  60928059-ef75-47b1-81e3-25ec1fb6ff10 |    0.00 | closed
  71ae151d-99c3-4505-8e33-9cda15fce302 |    1.25 | inactive
(3 rows)

> SHOW CREATE TABLE accounts;

  table_name |                create_statement
-------------+--------------------------------------------------
  accounts   | CREATE TABLE public.accounts (
             |     id UUID NOT NULL DEFAULT gen_random_uuid(),
             |     balance DECIMAL NULL,
             |     status public.status NULL,
             |     CONSTRAINT accounts_pkey PRIMARY KEY (id ASC)
             | )
(1 row)

> SELECT * FROM accounts WHERE status='open';

                   id                  | balance | status
---------------------------------------+---------+---------
  3848e36d-ebd4-44c6-8925-8bf24bba957e |  500.50 | open
(1 row)

Create a composite data type

Use the statements below to create a composite data type:

CREATE TYPE IF NOT EXISTS my_point AS (x INT, y INT);

CREATE TABLE IF NOT EXISTS points (
    id UUID DEFAULT gen_random_uuid(), p my_point,
    CONSTRAINT my_point_xcheck
        CHECK (((p).x >= 0) AND ((p).x <= 10000)),
    CONSTRAINT my_point_ycheck
        CHECK (((p).y >= 0) AND ((p).y <= 10000))
);

Insert 10,000 randomly generated values of type mypoint:

INSERT INTO points (p) SELECT (floor(random()*10000),floor(random()*10000))::MY_POINT FROM generate_series(1,10000);

SELECT * FROM points ORDER BY ((p).x) ASC LIMIT 25;

                   id                  |     p
---------------------------------------+------------
  434095ae-6297-490a-9040-278c749b4d29 | (0,9982)
  4f3155f6-d184-415b-98af-22b061da6623 | (1,5242)
  ff3eda0a-c144-43c4-9bbc-0c14e90a0349 | (1,4053)
  8a97e5f1-9db6-4458-8d3a-172f8624560c | (7,9857)
  1a599ed3-0a86-4096-851b-d5b35fb9cea4 | (7,599)
  5fc17aef-6de4-452c-a434-a709b2210938 | (9,9786)
  0967799c-c726-4f42-915e-4517ea3c589b | (9,458)
  6c1a46a9-66f8-4e96-8501-49fe6f77490c | (10,1447)
  a10824ec-3d17-4162-8457-372a76675a84 | (10,808)
  e7edf3fb-88f6-4675-9c79-44f74b7d0f49 | (11,1654)
  fd477aec-6685-4607-a940-7d4b1608913b | (11,6563)
  5806fcb5-28b6-4a94-b8dc-229fadb1d46d | (12,5519)
  a9545c0c-2b45-4e1f-bb19-e9a7a1630d6f | (12,4474)
  d591111b-a1c1-4168-8565-41f3d914dc6d | (12,7164)
  262482e5-d58d-463a-8a1d-ed66389e9424 | (15,3047)
  dfd75b68-1da0-4ea1-8e6e-66a9dd7389da | (16,1306)
  060070ee-40df-4c97-badb-9c08a2f54209 | (16,3016)
  33b77621-596b-4ee3-98e9-127ba942f1a7 | (16,2343)
  f3c7e11a-1784-4604-b083-e78dd32ab8b4 | (17,5155)
  485d45ae-0870-47b6-b986-4fabf0207644 | (17,4715)
  319f2184-3743-4fb2-8fe7-8efb0710bbee | (19,8570)
  ca55a46a-e5b6-4da5-8be5-575a39cea07c | (19,6667)
  f1ef70f6-0fb7-4338-b488-14cc6187e61b | (20,9113)
  3b18c682-967b-408d-8877-4ac102da3f12 | (21,5736)
  d0dd5914-a353-4603-a9a8-494f68db01f9 | (21,4328)
(25 rows)

Known limitations

Updating subfields of composite types using dot syntax results in a syntax error.
Tuple elements cannot be accessed without enclosing the composite variable name in parentheses. For example, (OLD).column and (NEW).column when used in triggers.

Statements

Syntax

Data Types

Spatial Data

Constraints

Synopsis

Parameters

Required privileges

Example

Create an enumerated data type

Create a composite data type

Known limitations

See also

​Synopsis

​Parameters

​Required privileges

​Example

​Create an enumerated data type

​Create a composite data type

​Known limitations

​See also

Synopsis

Parameters

Required privileges

Example

Create an enumerated data type

Create a composite data type

Known limitations

See also