SQL CREATE TYPE and Enums

CREATE TYPE gives a name to a type so tables can use it like a built-in one. The kind you will reach for most is an enum: a list of allowed values, such as an order's status. A column of that type accepts those values and nothing else, so a typo or an unexpected status is refused when it is written, not discovered in a report a month later.

create or replace type order_status as enum (
  'pending', 'processing', 'shipped', 'cancelled'
);

create or replace table typed_orders (
  order_id integer,
  customer_id integer,
  status order_status
);

insert into typed_orders
select order_id, customer_id, status
from orders;

select status, count(*) as orders
from typed_orders
group by status
order by status

The values read back as text, but they come out in the order the type lists them: pending, processing, shipped, then cancelled. On a plain text column the same query puts cancelled first. Listing the values in the order things happen is what makes that useful.

A value the list does not have

insert into typed_orders
values (100, 1, 'lost')

DuckDB answers Conversion Error: Could not convert string 'lost' to UINT8. The message is about the enum, even though it does not say so: DuckDB stores each value as a small number (a UINT8, one byte), and 'lost' has no number. Read to UINT8 in this error as "not one of the allowed values". Values are matched exactly, so 'Shipped' is refused the same way. The same message for numbers, dates, and other casts is covered on Could not convert string.

Build the list from the data

An enum written from memory is easy to get wrong. Garden Shop has no delivered status, and it does have processing:

create or replace type status_guess as enum (
  'pending', 'shipped', 'delivered', 'cancelled'
);

create or replace table guessed_orders (
  order_id integer,
  status status_guess
);

insert into guessed_orders
select order_id, status
from orders;

The insert fails on the first unknown value, with Could not convert string 'processing' to UINT8 when casting from source column status, and none of the 24 rows is written. Check what the column really holds before you define the type:

select status, count(*) as orders
from orders
group by status
order by status

When the order does not matter, DuckDB can build the list from a query directly:

create or replace type state_code as enum (
  select distinct state from customers order by state
);

select enum_range(null::state_code) as states;

Inspecting a type

select enum_range(null::order_status) as all_values,
       enum_first(null::order_status) as first_value,
       enum_last(null::order_status) as last_value

select type_name, logical_type
from duckdb_types()
where type_name in ('order_status', 'state_code')
order by type_name

DESCRIBE typed_orders shows the column as ENUM('pending', 'processing', 'shipped', 'cancelled'): the full list rather than the type's name. See SHOW TABLES and DESCRIBE.

Changing an enum

DuckDB has no ALTER TYPE. You replace the type, and that alone is not enough:

create or replace type order_status as enum (
  'pending', 'processing', 'shipped', 'cancelled', 'returned'
)

insert into typed_orders
values (200, 1, 'returned')

That fails with Could not convert string 'returned' to UINT8. A column copies the type's list when the column is created, so replacing the type leaves existing columns on the old list. Point the column at the new type:

alter table typed_orders
alter column status type order_status;

insert into typed_orders
values (200, 1, 'returned');

select status, count(*) as orders
from typed_orders
group by status
order by status;

returned now sorts last, after cancelled, because that is where the new list put it. The same copying explains DROP TYPE: it succeeds even while a table uses the type, and the table keeps working, still holding its own copy of the list. See ALTER TABLE.

Aliases and structured types

A type can also be another name for an existing type, which keeps a repeated definition in one place:

create or replace type money as decimal(12, 2);

create or replace table price_list (
  product_id integer,
  price money
);

insert into price_list
select product_id, price from products;

describe price_list;

The name is a shortcut, not a type of its own: DESCRIBE reports price as DECIMAL(12,2), and nothing in the table remembers it was declared as money.

create or replace type address as struct(city varchar, state varchar);

select customer_id,
       {'city': city, 'state': state}::address as home
from customers
order by customer_id
limit 3;

Read a field with a dot: home.city. A UNION type holds one value of several possible types and remembers which: union(email varchar, phone varchar) can store a customer's email or their phone, and union_tag says which one a row has. Both are DuckDB's nested types, useful for reshaping data, and much less common in schemas than enums.

What can go wrong

select count(*) as before_shipped
from typed_orders
where status < 'shipped'

You might expect the enum's order: pending and processing, 6 orders. DuckDB returns 8, because it compares an enum with a plain string as text, and cancelled and returned both sort before shipped alphabetically. EXPLAIN shows it: the filter reads CAST(status AS VARCHAR) < 'shipped'. Cast the literal to the enum to compare by its order:

select count(*) as before_shipped
from typed_orders
where status < 'shipped'::order_status

That returns 6. Sorting is the opposite case: ORDER BY status uses the enum's order, and ORDER BY status::varchar gets you alphabetical. See EXPLAIN for reading the filter.

• The error does not name the enum. Could not convert string 'x' to UINT8 is the only sign; it names neither the type nor its allowed values. Check enum_range.
• Replacing the type changes nothing in existing tables. Each column keeps the list it was created with until you alter it.
• Every new value is a schema change. If the list grows every month, an enum costs more than it protects.

Enum, CHECK constraint, or lookup table?

Across databases