Sequences, Enums & Custom Types

Tutorial 10 of 11

Every SERIAL column you have used in previous tutorials is backed by a sequence—a named counter that generates unique integers. This tutorial peels back the abstraction, shows you how to create and control sequences directly, and introduces enum types for columns with a fixed set of allowed values.

1. SERIAL under the hood

SERIAL is syntactic sugar. When you write id SERIAL PRIMARY KEY, mskql creates a sequence named <table>_<column>_seq and sets the column’s default to NEXTVAL('<seq>').

CREATE TABLE items (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

INSERT INTO items (name) VALUES ('Alpha'), ('Beta'), ('Gamma');

SELECT * FROM items ORDER BY id;

2. CREATE SEQUENCE explicitly

Create a sequence with custom start, increment, and bounds. This is useful when you need invoice numbers starting at 1000, or IDs that increment by 10.

CREATE SEQUENCE invoice_seq START 1000 INCREMENT 1;

CREATE TABLE invoices (
    id       INT PRIMARY KEY DEFAULT NEXTVAL('invoice_seq'),
    customer TEXT NOT NULL,
    amount   INT NOT NULL
);

INSERT INTO invoices (customer, amount) VALUES
    ('Acme Corp',  500),
    ('Globex Inc', 1200),
    ('Initech',    750);

SELECT * FROM invoices ORDER BY id;

3. Using NEXTVAL directly

You can call NEXTVAL in any expression—not just as a column default. This is useful for generating IDs before inserting, or for numbering rows in a query.

CREATE SEQUENCE batch_seq START 1;

SELECT NEXTVAL('batch_seq') AS batch_1;
SELECT NEXTVAL('batch_seq') AS batch_2;
SELECT NEXTVAL('batch_seq') AS batch_3;

Each call returns the next value: 1, 2, 3. The sequence never goes backward—even if a transaction rolls back, consumed values are gone.

4. CREATE TYPE AS ENUM

An enum type restricts a column to a fixed set of string values. Unlike a CHECK constraint, the allowed values are defined once and reused across multiple tables. Internally, mskql stores enums as 4-byte ordinals—not strings—so comparisons and sorting are integer-fast.

CREATE TYPE priority AS ENUM ('low', 'medium', 'high', 'critical');
CREATE TYPE status AS ENUM ('open', 'in_progress', 'resolved', 'closed');

CREATE TABLE tickets (
    id       SERIAL PRIMARY KEY,
    title    TEXT NOT NULL,
    priority priority NOT NULL DEFAULT 'medium',
    status   status NOT NULL DEFAULT 'open'
);

INSERT INTO tickets (title, priority, status) VALUES
    ('Login page broken',     'critical', 'open'),
    ('Update docs',           'low',      'in_progress'),
    ('Add dark mode',         'medium',   'open'),
    ('Fix memory leak',       'high',     'resolved'),
    ('Refactor auth module',  'medium',   'in_progress'),
    ('Upgrade dependencies',  'low',      'closed');

5. Querying enum columns

Enum values behave like strings in WHERE clauses and ORDER BY. Ordering follows the declaration order (low < medium < high < critical), not alphabetical.

SELECT title, priority, status
FROM tickets
WHERE priority IN ('high', 'critical')
ORDER BY priority, title;

6. Aggregating by enum

Group by an enum column to get counts per status or priority level.

SELECT status,
       COUNT(*) AS ticket_count
FROM tickets
GROUP BY status
ORDER BY status;

7. Updating enum values

Update an enum column just like a text column. The value must be one of the declared enum values—anything else is an error.

UPDATE tickets
SET status = 'resolved',
    priority = 'high'
WHERE title = 'Login page broken'
RETURNING title, priority, status;

8. Drop a sequence or type

Clean up with DROP SEQUENCE and DROP TYPE. You must drop tables using the type first.

DROP SEQUENCE batch_seq;

The sequence is removed. Any column defaulting to NEXTVAL('batch_seq') will error on the next insert.

What you learned

In this tutorial you explored the type system behind auto-incrementing IDs and constrained string columns:

• SERIAL is syntactic sugar for a sequence + default
• CREATE SEQUENCE with custom START and INCREMENT
• NEXTVAL('seq') to consume the next value from a sequence
• CREATE TYPE AS ENUM for fixed-set string columns
• Enum ordering follows declaration order, not alphabetical
• Enum storage: 4-byte ordinals internally, string display externally
• DROP SEQUENCE / DROP TYPE for cleanup