CREATE TABLE and choosing data types

CREATE TABLE is where you make the decisions that are cheap now and expensive later. A query you got wrong costs you an afternoon. A column type you got wrong costs you a migration, a deployment window and a conversation about downtime.

The statement

CREATE TABLE members (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name          text        NOT NULL,
  email         text        UNIQUE,
  phone         text,
  pincode       text,
  joined        date        NOT NULL,
  membership    text        NOT NULL DEFAULT 'standard'
                            CHECK (membership IN ('standard', 'student', 'senior')),
  date_of_birth date
);

Column name, type, then any constraints. That is the whole shape.

Read it as a set of claims about the world: every member has a name and a joining date; an email is optional but no two members share one; membership is one of exactly three values and defaults to standard.

Those claims are enforced by the database, against every program, script and person that ever writes to it. That is the difference from validating in application code.

Choosing types: the short version

The long version is two lessons away. The decisions that matter most:

text            -- all strings. Not varchar(n) unless a real limit exists.
integer         -- whole numbers up to ~2.1 billion
bigint          -- whole numbers up to ~9.2 quintillion. Use for ids.
numeric(12,2)   -- exact decimals. For money, if you are not using integer paise.
boolean         -- true/false/NULL
date            -- a calendar day, no time
timestamptz     -- an instant. Almost always this, never plain timestamp.
jsonb           -- structured data you will query into
uuid            -- a 128-bit identifier

Three rules to take now:

text, not varchar(n). In PostgreSQL they are the same speed, and varchar(50) is a guess about the future that will be wrong. A length limit is a business rule — put it in a CHECK where it is visible, or leave it out.

timestamptz, never timestamp. The next lesson but one shows what the difference does to you.

Money is integer paise or numeric. Never float. Also two lessons away, with the numbers.

NOT NULL is the default you want

name  text NOT NULL,      -- required
phone text                -- optional

Nullable is PostgreSQL's default, and required should be yours. For each column ask: what does it mean for this to be unknown? If you cannot answer, it is NOT NULL.

From the NULL lesson: returned_on being NULL means "has not been returned", which is a real meaning. name being NULL means nothing useful.

Adding NOT NULL later to a table with rows is possible and awkward — the last lesson of this module covers it. Getting it right now is free.

DEFAULT

membership text        NOT NULL DEFAULT 'standard',
created_at timestamptz NOT NULL DEFAULT now(),
copies     int         NOT NULL DEFAULT 1

The value used when the column is not mentioned in an INSERT:

INSERT INTO members (name, joined) VALUES ('Nobody Here', current_date);
-- membership is 'standard'

DEFAULT now() is evaluated per row at insert time, not once when the table was created. That surprises people the first time and is what you want.

A DEFAULT does not backfill. Adding a default to an existing column does not change the rows already there.

And DEFAULT plus NOT NULL together mean the column is always present and you never have to think about it — which is the right combination for created_at and for counters.

Auto-incrementing keys

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

This is the modern form. You will also see the older one everywhere:

id bigserial PRIMARY KEY          -- older; avoid in new tables

They both give you an auto-incrementing number. The difference matters, and it is demonstrable.

With GENERATED ALWAYS, the database refuses to let you supply the value:

INSERT INTO t3 (id, x) VALUES (5, 'forced');

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

With bigserial, the same insert succeeds — and quietly breaks the table:

CREATE TEMP TABLE t4 (id bigserial PRIMARY KEY, x text);
INSERT INTO t4 (id, x) VALUES (1, 'a');    -- accepted; the sequence is untouched
INSERT INTO t4 (x)     VALUES ('b');       -- the sequence hands out 1

ERROR:  duplicate key value violates unique constraint "t4_pkey"
DETAIL:  Key (id)=(1) already exists.

The sequence did not know about the forced value, so the next automatic id collides. This is one of the most common ways a restored database or a data import breaks, and the fix — setval() on the sequence — is something you have to know to look for.

GENERATED ALWAYS AS IDENTITY makes the failure impossible. Use it. GENERATED BY DEFAULT AS IDENTITY is the middle option that permits an override when you explicitly ask.

Naming

CREATE TABLE book_categories (...)     -- lowercase, underscores, plural table

Lowercase with underscores. Unquoted identifiers are folded to lowercase anyway, so CREATE TABLE BookCategories gives you bookcategories. Quoting makes them case-sensitive and permanently annoying — SELECT * FROM "BookCategories" forever.

Conventions worth adopting, all of which the course dataset follows:

tables        plural       books, members, loans
columns       singular     title, member_id
primary key   id
foreign key   <table>_id   member_id, book_id
join table    both, joined book_categories
booleans      is_ / has_   is_active, has_fine
timestamps    _at          created_at, returned_at
dates         _on          borrowed_on, due_on

The _at and _on distinction is worth the discipline: _at is a timestamptz, _on is a date. A reader knows the type from the name, and the Full-Stack course has a whole lesson on the bug that follows from confusing the two.

The rest of the statement

CREATE TABLE IF NOT EXISTS books (...);     -- no error if it already exists
DROP TABLE books;                            -- gone, with its data
DROP TABLE IF EXISTS books CASCADE;          -- and anything referencing it
CREATE TEMP TABLE scratch (...);             -- disappears when you disconnect

CASCADE is worth respecting. It drops the dependent foreign keys — and in other contexts, dependent rows. Read what it will do before running it on anything you care about.

CREATE TEMP TABLE is genuinely useful for working through a problem: it exists only for your session and cleans itself up.

A table that says what it means

Compare:

-- says almost nothing
CREATE TABLE loans (
  id int, book int, member int, borrowed varchar(50),
  due varchar(50), returned varchar(50), fine float
);

-- says what it means, and enforces it
CREATE TABLE loans (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id     bigint NOT NULL REFERENCES books(id),
  member_id   bigint NOT NULL REFERENCES members(id),
  borrowed_on date   NOT NULL,
  due_on      date   NOT NULL,
  returned_on date,
  fine_paise  int    CHECK (fine_paise >= 0),
  CHECK (due_on >= borrowed_on),
  CHECK (returned_on IS NULL OR returned_on >= borrowed_on)
);

The second refuses a loan for a book that does not exist, a due date before the borrow date, a negative fine, and a return before the borrow. The first accepts 'next tuesday' as a due date and -4.7999999 as a fine.

Every one of those constraints is a bug that can now never reach your data, from any source, forever. That is the return on twenty extra minutes at design time.

Note the last two: a CHECK written at the table level rather than beside a column can refer to more than one column. That is how you express a rule about the relationship between values.

Check your work

What a table definition is. A set of enforced claims about the world.

text or varchar(n). text — same speed, and a length is a business rule that belongs in a CHECK.

The default you should want. NOT NULL, unless you can say what unknown means.

When DEFAULT now() is evaluated. Per row at insert time.

Whether a DEFAULT backfills. No.

Why GENERATED ALWAYS AS IDENTITY beats bigserial. serial lets you supply an id without advancing the sequence, so the next automatic insert collides.

What that error looks like. duplicate key value violates unique constraint.

Why not to quote identifiers. They become case-sensitive forever.

The _at and _on convention. _at is a timestamptz, _on is a date.

What a table-level CHECK can do. Refer to more than one column.

Practice

1. Write CREATE TABLE for a publishers table with an id, a name, a city and a founding year. Decide NOT NULL for each and justify it.
2. Create a table with varchar(5) and insert eight characters. Read the error.
3. Create the same with text and a CHECK (length(a) <= 5). Compare the errors.
4. Create a table with DEFAULT now(), insert two rows a few seconds apart, and compare the timestamps.
5. Add a DEFAULT to an existing column and confirm the existing rows did not change.
6. Create a table with GENERATED ALWAYS AS IDENTITY and try to insert an explicit id.
7. Create the same with bigserial, insert an explicit id of 1, then insert without one. Read the error.
8. Look up setval and fix that sequence.
9. Create CREATE TABLE BookTest and then try SELECT * FROM BookTest and SELECT * FROM "BookTest". Explain.
10. Write the loans table with all four constraints. Then try to insert: a bad book id, a due date before the borrow date, a negative fine, and a return before the borrow.
11. Create a temp table, disconnect, reconnect, and look for it.
12. Take a table from any project of yours and rewrite its definition with every constraint you can justify.

Official documentation

• PostgreSQL — CREATE TABLE — The full statement, including every constraint form.
• PostgreSQL — Data definition — Defaults, constraints and table basics as a guide rather than a reference.
• PostgreSQL — Identity columns — ALWAYS versus BY DEFAULT.
• PostgreSQL — Serial types — Including the note that identity columns are now preferred.

Next: primary keys, and why not to use a business value.