CREATE TABLE Done Right

CREATE TABLE is how you tell PostgreSQL the shape a new table must have. Done right, it names the table clearly, gives each column an honest type, uses defaults for facts the database can supply, and makes NULL something you choose on purpose.

Until now, the course handed you tables through datasets and setup scripts. Section 9 unlocks DDL, short for data definition language — SQL that creates and changes database objects instead of reading or writing rows.

The first table: columns and types

A table definition is a contract. The table name says what collection of rows exists; each column says what fact each row may store; each type says what kind of value fits.

Here the cafe starts a tiny drink queue from an empty database:

CREATE TABLE shift_drinks (
  drink_name text,
  size_name text,
  price numeric(5,2),
  ordered_at timestamp
);
 
INSERT INTO shift_drinks (drink_name, size_name, price, ordered_at)
VALUES
  ('Espresso', 'small', 2.50, TIMESTAMP '2026-05-12 08:05:00'),
  ('Cold Brew', 'large', 4.50, TIMESTAMP '2026-05-12 08:07:00');
 
SELECT drink_name, size_name, price, ordered_at
FROM shift_drinks
ORDER BY ordered_at;

CREATE TABLE
INSERT 0 2
 drink_name | size_name | price |     ordered_at
------------+-----------+-------+---------------------
 Espresso   | small     |  2.50 | 2026-05-12 08:05:00
 Cold Brew  | large     |  4.50 | 2026-05-12 08:07:00
(2 rows)

You already know the type families from Section 3: text for open-ended text, numeric(5,2) for exact money-like decimals, and timestamp for a wall-clock date and time. The new habit is choosing the smallest honest container at table-creation time, because the table will enforce that promise for every future writer.

Defaults and the NULL stance

A default is a value the table supplies when an insert omits a column. Defaults are perfect for facts the database owns: a starting status, a fixed lab date, or a boolean flag that begins false.

This table also uses NOT NULL. NOT NULL is a constraint, a rule the database enforces, that says the column must contain a real value. Constraints get their full lesson in 9.2; today, learn the stance: make columns NOT NULL by default, then argue for each nullable column.

CREATE TABLE prep_tasks (
  task_name text NOT NULL,
  station text NOT NULL,
  due_on date NOT NULL DEFAULT DATE '2026-05-12',
  done boolean NOT NULL DEFAULT false
);
 
INSERT INTO prep_tasks (task_name, station)
VALUES ('Fill espresso hopper', 'bar');
 
SELECT task_name, station, due_on, done
FROM prep_tasks;

CREATE TABLE
INSERT 0 1
      task_name       | station |   due_on   | done
----------------------+---------+------------+------
 Fill espresso hopper | bar     | 2026-05-12 | f
(1 row)

That row did not provide due_on or done; the table did. It did provide task_name and station, because those are required. Miss one and PostgreSQL refuses the row:

INSERT INTO prep_tasks (task_name)
VALUES ('Wipe patio tables');

ERROR:  null value in column "station" of relation "prep_tasks" violates not-null constraint
DETAIL:  Failing row contains (Wipe patio tables, null, 2026-05-12, f).

The analogy is the schema as a receiving checklist: if the cafe cannot prep a task without knowing its station, the database should not accept that row and leave everyone guessing later.

Names that survive more than one week

Naming is not cosmetic. It decides whether your future joins read like English or like a puzzle.

Use snake_case for tables and columns, because it works cleanly without quotes. Prefer plural table names for collections in this course: prep_tasks, shift_drinks, customers. Pick singular or plural in your own team, then stay consistent. Avoid reserved words and vague nouns: orders beats quoted "order", and prep_tasks beats data.

Column names should carry the fact, not the table name repeated back at you. Inside prep_tasks, station is clear; prep_tasks_station is noise. When two tables meet, aliases and qualification will carry the context.

TEMP tables and CTAS

A temporary table is a table that lives only for your current session. It is useful for scratch work: try a shape, hold a filtered subset, or debug a multi-step idea without committing to the real schema.

CTAS means CREATE TABLE AS SELECT, a shortcut that builds a table from a query's result. Pair it with TEMP for scratch copies:

CREATE TEMP TABLE morning_drinks AS
SELECT sd.drink_name,
       sd.price
FROM shift_drinks AS sd
WHERE sd.ordered_at < TIMESTAMP '2026-05-12 09:00:00'
ORDER BY sd.drink_name;
 
SELECT drink_name, price
FROM morning_drinks
ORDER BY drink_name;

CREATE TABLE
 drink_name | price
------------+-------
 Cold Brew  |  4.50
 Espresso   |  2.50
(2 rows)

The command tag is CREATE TABLE because CTAS created a table from the rows produced by the SELECT. The scratch table lives for the current session, and this playground's setup drops it before each Run so your script can recreate it cleanly.

Use CTAS for scratch tables and derived copies. Use full CREATE TABLE (...) for tables that matter, because you need to choose names, types, defaults, and constraints deliberately.

Drop and recreate while the design is still wet

During development, you often get the shape wrong on the first try. That is normal. The safe lab rhythm is: drop the draft table, recreate it with the better definition, then inspect the schema.

DROP TABLE removes the table object, not just its rows. Use it freely in this playground and in throwaway development databases; do not aim it at production tables without a migration plan.

DROP TABLE shift_drinks;
 
CREATE TABLE shift_drinks (
  drink_name text NOT NULL,
  size_name text NOT NULL,
  price numeric(5,2) NOT NULL DEFAULT 0,
  ordered_at timestamp NOT NULL
);
 
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'shift_drinks'
ORDER BY ordinal_position;

DROP TABLE
CREATE TABLE
 column_name |          data_type          | is_nullable | column_default
-------------+-----------------------------+-------------+----------------
 drink_name  | text                        | NO          | NULL
 size_name   | text                        | NO          | NULL
 price       | numeric                     | NO          | 0
 ordered_at  | timestamp without time zone | NO          | NULL
(4 rows)

information_schema is a standard set of catalog views you can query — views over metadata that describe tables, columns, and other database objects. It is the SQL cousin of the schema bar: table shape as data you can query.

CREATE TABLE gives you the shape. Next, 9.2 turns constraints into the database's last line of defense: primary keys, foreign keys, unique rules, and checks that refuse bad data before it spreads.