ENUM

An ENUM, short for enumeration, is a data type in DuckDB and many other database systems that allows you to define a fixed set of named values. It's particularly useful when you have a column that should only contain a limited number of distinct values. ENUMs are more memory-efficient than storing strings and can improve query performance.

In DuckDB, you can create an ENUM type using the following syntax:

Copy code
CREATE TYPE mood AS ENUM ('happy', 'neutral', 'sad');

Once defined, you can use this ENUM type in table definitions:

Copy code
CREATE TABLE person (
    id INTEGER,
    name VARCHAR,
    current_mood mood
);

You can then insert values using the defined ENUM values:

Copy code
INSERT INTO person VALUES (1, 'Alice', 'happy');
INSERT INTO person VALUES (2, 'Bob', 'sad');

ENUMs provide type safety, ensuring that only predefined values can be inserted into the column. They also allow for efficient sorting based on the order of definition. However, it's important to note that adding new values to an existing ENUM type can be challenging, so careful planning is necessary when using this data type in your schema design.