SQL CREATE TABLE

CREATE TABLE declares a table by listing its columns and their types. Everything else — defaults, keys, checks — hangs off that list.

create table order_imports (
  order_id integer,
  customer_email varchar,
  order_date date,
  quantity integer,
  unit_price decimal(10, 2)
)

Compare that withCREATE TABLE AS SELECT, which infers everything from a query. Declaring the table yourself is more typing and buys you two things: the types are what you chose rather than what a query happened to produce, and you get somewhere to put the rules.

Types worth knowing

Defaults

A DEFAULT fills a column when the insert does not mention it. It applies on insert only — it never rewrites rows that are already there.

create or replace table order_imports (
  order_id integer,
  status varchar default 'pending',
  loaded_at timestamp default current_timestamp,
  quantity integer default 1
)

A default expression is evaluated per row at insert time, socurrent_timestamp records when each row arrived rather than when the table was created.

Rules on the table

Constraints move a rule out of every query that touches the data and into the table itself, where it cannot be forgotten. Theconstraints reference covers each one and the error it raises; the short version:

create or replace table order_imports (
  order_id integer primary key,
  customer_email varchar not null,
  status varchar default 'pending',
  quantity integer check (quantity > 0),
  coupon_code varchar unique
)

Rerunning the statement

CREATE TABLE on a name that already exists is an error. Two ways around it, and they are not interchangeable.

-- leaves the existing table exactly as it is
create table if not exists order_imports (
  order_id integer
);

-- throws the existing table away, rows included
create or replace table order_imports (
  order_id integer,
  customer_email varchar
);

Generated columns

A generated column is defined by an expression over the other columns. DuckDB computes it on read, so it never falls out of step with the values it is derived from.

create table line_items (
  order_id integer,
  quantity integer,
  unit_price decimal(10, 2),
  line_total as (quantity * unit_price)
)

You cannot insert into a generated column — DuckDB answersCannot insert into a generated column — which is the point. Other engines let you add STORED to keep the computed value on disk; DuckDB supports virtual generated columns only and rejectsSTORED.

Checking what you built

describe order_imports

DESCRIBE lists columns, types, and defaults.duckdb_constraints() lists the rules, andinformation_schema.columns is the portable equivalent of the first.

Habits worth keeping

• Name tables for what one row is — orders,line_items — so a join reads like a sentence.
• Pick decimal for anything anyone will add up as money.
• Declare the table when it will receive writes; use CTAS when you are storing the answer to a query.
• Put the rule in the table, not in a comment about the table. ACHECK outlives the person who knew about it.