Reference

SQL CREATE TABLE

Declaring a table up front is how you say what the data is allowed to look like, before any of it arrives.

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

Practice

Practice choosing column types and defaults

Basic shape

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

Compare that with CREATE 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

Type	Use it for
`integer`, `bigint`	Counts, IDs, quantities.
`decimal(p, s)`	Money. Exact to the digit you specify.
`double`	Measurements and ratios where tiny rounding error is acceptable.
`varchar`	Any text. No length needed.
`date`, `timestamp`	Calendar days, and points in time.
`boolean`	True/false flags.

Watch out

Use decimal for money, not double. A double stores 0.1 as the nearest binary approximation, so totals drift by fractions of a cent and a sum that should be 100.00 compares as unequal to 100.00. Reach for decimal(10, 2) and the arithmetic stays exact.

Defaults

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

Values that fill themselves in

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, so current_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. The constraints reference covers each one and the error it raises; the short version:

A table that defends itself

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.

Two guards, two meanings

-- 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
);

Watch out

IF NOT EXISTS is silent about the table it skipped. If you edit the column list and rerun a script written that way, nothing changes and nothing complains. You are still on the old shape. Use CREATE OR REPLACE TABLE when the definition is the thing you changed, and remember it discards the rows.

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.

Derived at read time

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 answers Cannot 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 rejects STORED. See generated columns for what the expression may use and how each database spells it.

Checking what you built

Read the table back

describe order_imports

DESCRIBE lists columns, types, and defaults. duckdb_constraints() lists the rules, and information_schema.columns is the portable equivalent of the first. See SHOW TABLES and DESCRIBE for the rest of the catalog.

Dialect note

Type names are the least portable part of SQL. PostgreSQL accepts varchar but idiom favors text, and BigQuery has only string; auto-incrementing IDs are serial, auto_increment, or identity depending on the engine. DuckDB uses a sequence for that. The structure of CREATE TABLE ports cleanly; the type names rarely do.

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. A CHECK outlives the person who knew about it.