CREATE TABLE in SQL

Every table so far came with the dataset, or with a starter that built it for you. Making your own takes one statement: CREATE TABLE, a name, and a list of columns, each with a type. Here the shop starts a log of visits to its suppliers:

create or replace table supplier_visits (
  visit_id    integer,
  supplier_id integer,
  visited_on  date,
  notes       varchar,
  follow_up   boolean default false
);

insert into supplier_visits (visit_id, supplier_id, visited_on, notes)
values (1, 4, '2024-04-18', 'New orchid line in June.');

select * from supplier_visits;

Text accepts anything, and that's the problem

A first draft often makes every column varchar, because text accepts whatever arrives. It also accepts 'five' as a rating, and the cost shows up when you use the values. On a text column, avg(rating) fails with No function matches the given name and argument types 'avg(VARCHAR)', and reviewed_on + 14 fails the same way. DuckDB won't guess that text holds a number or a date.

With the right types, the bad value is stopped at the door instead. Inserting 'five' into an integer column fails with Conversion Error: Could not convert string 'five' to INT32, and 'May 2' into a date column fails because it isn't YYYY-MM-DD. One conversion doesn't fail: 4.5 inserted into an integer column is stored as 5, without a warning. Lesson 2.7 · Integers, decimals, and floats covers which type keeps which digits.

A default fills what the INSERT leaves out

follow_up boolean default false means a row inserted without a follow_up value gets false. Without the default, it gets NULL, and a later where not follow_up skips that row, because not NULL is still unknown.

The default applies only when the column is left out of the INSERT. An INSERT that names the column and passes null stores NULL. Lesson 8.9 · Rules with constraints shows how to forbid that.

Running it twice

A plain create table fails on its second run with Table with name "supplier_visits" already exists! create or replace table throws the old table away, rows included, and builds the new one. That's what you want while you are still deciding on the columns, and it's why the exercises in this chapter use it. Never point it at a table whose rows you need. Lesson 18.5 · Write a rerunnable script compares it with IF NOT EXISTS.

Check what you built

DESCRIBE lists a table's columns, their types, and their defaults. Run it after a CREATE TABLE to confirm the table is the shape you meant:

describe supplier_visits;

Don't trust the results grid for this. A date stored as text displays as 2024-04-18, exactly like a real date. Inside a query, typeof(visited_on) from Lesson 2.7 · Integers, decimals, and floats tells them apart: it says DATE for one and VARCHAR for the other.