DataslopeCreating Tables
How to define a table's shape with CREATE TABLE, choose columns and types, and add simple rules that keep your data clean.
So far, the runnable blocks have quietly created tables for you with
their initSql. Now you will create them yourself. Defining a table
is how you give a database its structure — and it is more
interesting than it sounds, because a table definition is really a
set of promises about your data.
The CREATE TABLE statement
To make a table, you list its columns and each column's type inside
CREATE TABLE:
Read it as a sentence: "Create a table called books with an
integer id, a text title, a text author, and an integer
year." The parentheses hold a comma-separated list of
column_name TYPE pairs. That is the core of the whole statement.
Adding rules with constraints
Beyond a name and type, each column can carry constraints — rules the database enforces on every row. They turn a table from a loose grid into a guardrail for clean data. The common ones:
| Constraint | Meaning |
|---|---|
PRIMARY KEY | Uniquely identifies each row (unique + not null) |
NOT NULL | This column can never be empty |
UNIQUE | No two rows may share this value |
DEFAULT x | If no value is given, use x |
CHECK (...) | The value must satisfy a condition |
Here is the same table, upgraded with rules that express real intent about the data:
Look at the result: even though we never supplied a genre, the row
has Unknown, because of DEFAULT 'Unknown'. The table filled in
the blank for us. Meanwhile title NOT NULL guarantees no book can
ever be saved without a title, and CHECK (year > 0) blocks
nonsensical years.
Constraints are documentation that bites
A good set of constraints tells the next person (and future you) what the data means — "a book must have a title," "a year is positive." Unlike a comment, the database actually enforces these promises, so they can never quietly drift out of date.
Auto-generating ids with SERIAL
Typing id values by hand is tedious and error-prone. PostgreSQL
can generate them for you. Declaring a column as SERIAL makes the
database assign the next number automatically on each insert:
This is the everyday way to create surrogate primary keys (from the
Primary Keys page): a SERIAL PRIMARY KEY gives every row a
guaranteed-unique id without you tracking the numbers yourself.
Removing a table
To delete an entire table and all its rows, use DROP TABLE. Handle
with care — it is permanent.
DROP TABLE books;Check your understanding
What does CREATE TABLE do?
It inserts new rows into an existing table.
It defines a new table's structure — its name, columns, and their types and rules.
It permanently deletes a table.
It runs a search over the data.
A column is declared genre TEXT DEFAULT 'Unknown'. A row is inserted without providing a genre. What is stored?
The row is rejected because genre is missing.
An empty/null genre.
The value 'Unknown', because DEFAULT supplies it when no value is given.
A random genre chosen by the database.
Why is declaring a column as SERIAL PRIMARY KEY so common?
It encrypts the column for security.
It sorts the table by that column automatically.
PostgreSQL automatically assigns the next unique number, giving each row a stable surrogate key without manual tracking.
It allows the column to store text and numbers together.
Your First Queries
Run real SQL right now — compute values, name results, and understand the anatomy of a basic SELECT before we touch tables.
Inserting, Updating, and Deleting Data
How rows get into a table and how they change — INSERT, UPDATE, and DELETE — and why WHERE is the most important word on this page.