CREATE TYPE

The CREATE TYPE creates a new type in a . 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 .

Synopsis

Parameters

Parameter	Description
type\_name	The name of the type. You can qualify the name with a (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 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 on the type.

Example

Create an enumerated data type

Use the statements below to create an data type.

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

To see all user-defined data types, use :

> SHOW TYPES;

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

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

> 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 name in parentheses. For example, (OLD).column and (NEW).column when used in .

See also