Time to build something. In this lesson you create a role and a database for Orbit, learn how schemas organise objects, and create the first two tables: users and notes. Every later lesson builds on them.
Create the Orbit role and database
Connect with the role you created in lesson 2 and run:
CREATE ROLE orbit LOGIN PASSWORD 'change-me';
GRANT orbit TO CURRENT_USER;
CREATE DATABASE orbit OWNER orbit;
ALTER DATABASE orbit SET timezone TO 'UTC';
CREATE ROLE
GRANT ROLE
CREATE DATABASE
ALTER DATABASE
orbitis the owner role. It owns the database and, soon, every table in it. Applications will connect with a separate, less powerful role (lesson 15).GRANT orbit TO CURRENT_USERmakes you a member oforbit. Since PostgreSQL 16, you may only create a database owned by another role if you are allowed to act as that role.ALTER DATABASE ... SET timezonemakes every new session in this database use UTC. Storing and showing times in UTC on the server is a good default, and it makes the results in this course match yours exactly.
Now connect to the new database as the new role. Use localhost so the connection goes over TCP and asks for the password; over the local socket, Linux packages use peer authentication, which only lets the OS user orbit in:
\c orbit orbit localhost
From the shell, the same connection is psql -h localhost -U orbit -d orbit. Add a line to ~/.pgpass (lesson 3) so you are not asked for the password every time.
Tip: CREATE DATABASE copies a template database, template1 by default. Anything you put in template1, such as an extension, appears in every database created afterwards.
Schemas and the search_path
A database contains schemas, and objects live in schemas. Every database starts with one called public. When you write notes, Postgres looks for it in the schemas listed in search_path:
SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
"$user" means “a schema with the same name as the current role, if it exists”. So for the role orbit, a table called notes is looked up first in a schema orbit and then in public. You can always write the full name, public.notes, to be explicit.
Schemas are useful to separate parts of an application, or to give a reporting team its own space:
CREATE SCHEMA reporting;
CREATE TABLE reporting.daily_signups (day date PRIMARY KEY, total int NOT NULL);
SET search_path TO reporting, public;
Since PostgreSQL 15, ordinary roles can no longer create objects in public unless they own the database or are granted that right, which closes a long-standing security gap. orbit owns the database, so it can.
For this course, Orbit keeps its tables in public. Reset the path:
RESET search_path;
DROP SCHEMA reporting CASCADE;
Design the first tables
Orbit has users, and users write notes. Each note belongs to exactly one user:
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE notes (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
title text NOT NULL,
body text NOT NULL DEFAULT '',
pinned boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE
CREATE TABLE
A lot happens in those few lines:
| Piece | Meaning |
|---|---|
bigint GENERATED ALWAYS AS IDENTITY | Postgres numbers rows 1, 2, 3… using a sequence it manages for you |
PRIMARY KEY | The column uniquely identifies a row; it is NOT NULL and gets a unique index |
NOT NULL | The column must always have a value |
UNIQUE | No two rows may have the same email; also creates an index |
DEFAULT now() | Value used when an INSERT does not provide one |
REFERENCES users (id) | A foreign key: every user_id must exist in users |
ON DELETE CASCADE | Deleting a user deletes their notes too |
Lessons 5 and 10 explain types and constraints in depth. For now, notice that the database itself enforces the rules: no application bug can create a note for a user that does not exist.
Look at what you created
psql describes a table with \d:
\d notes
It prints the columns with types, nullability and defaults, then the indexes (notes_pkey), the foreign key and any tables that reference this one. \dt lists all tables and \dt+ adds their size.
The same information is available with SQL from the standard information_schema views, which is handy in scripts and works the same in other databases:
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;
column_name | data_type | is_nullable | column_default
-------------+--------------------------+-------------+----------------
id | bigint | NO | (null)
email | text | NO | (null)
name | text | NO | (null)
created_at | timestamp with time zone | NO | now()
(4 rows)
The identity column shows no default: identity is a property of the column, not a default expression. Postgres’s own catalog tables (pg_class, pg_attribute, pg_constraint and friends) hold even more detail and are what \d reads behind the scenes.
Changing a table
Requirements change, and ALTER TABLE changes tables in place, keeping the data:
ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users RENAME COLUMN bio TO about;
ALTER TABLE users ALTER COLUMN about SET DEFAULT '';
ALTER TABLE users DROP COLUMN about;
A few things to know before you run ALTER TABLE on a big, busy table:
- Adding a column with no default, or with a constant default, is instant even on a billion rows.
- Changing a column’s type (for example
inttobigint) usually rewrites the whole table while holding a lock that blocks reads and writes. - Every
ALTER TABLEneeds a brief exclusive lock. If a long query is using the table, yourALTERwaits, and every new query queues behind it. Setlock_timeoutfirst so you fail fast instead (lesson 12).
Removing tables
DROP TABLE IF EXISTS scratch;
DROP TABLE IF EXISTS old_reports CASCADE;
TRUNCATE TABLE import_staging;
DROP TABLE removes the table and its data. CASCADE also drops objects that depend on it, such as views; run it once without CASCADE first to see what would go. TRUNCATE keeps the table but removes every row instantly.
Warning: There is no recycle bin. DROP and TRUNCATE are permanent once committed. Inside BEGIN ... ROLLBACK you can still undo them, because DDL in Postgres is transactional.
Documenting tables
Comments live in the database next to the objects, where tools and \d+ can show them:
COMMENT ON TABLE notes IS 'Notes written by Orbit users';
COMMENT ON COLUMN notes.pinned IS 'Pinned notes are shown first in the app';
Naming conventions that age well
- Use lower_snake_case for every name. Unquoted names are folded to lower case, so
CreatedAtsilently becomescreatedat. Quoting ("CreatedAt") preserves case but then you must quote it forever. - Name tables in the plural (
users,notes) and foreign keys as<singular>_id(user_id). - Prefer
textovervarchar(n), and add aCHECKconstraint when you really need a length limit (lesson 5). - Give every table a primary key, and time columns a
_at(timestamps) or_on(dates) suffix.
Quick reference
| Task | SQL |
|---|---|
| Create a login role | CREATE ROLE orbit LOGIN PASSWORD '...'; |
| Create a database | CREATE DATABASE orbit OWNER orbit; |
| Per-database setting | ALTER DATABASE orbit SET timezone TO 'UTC'; |
| Create a schema | CREATE SCHEMA reporting; |
| Lookup order | SHOW search_path; / SET search_path TO ...; |
| Create a table | CREATE TABLE ... ( ... ); |
| Add / rename / drop a column | ALTER TABLE ... ADD / RENAME / DROP COLUMN ...; |
| Describe | \d notes |
| Empty a table | TRUNCATE notes; |
| Remove a table | DROP TABLE IF EXISTS notes; |
Exercise
A teammate creates a column named CreatedAt without quotes. What is the column called in the database?
Show answer
Answer: C. Unquoted identifiers are folded to lower case. Only a quoted name such as “CreatedAt” keeps its case, and then it must be quoted in every query.
Exercise
You run SELECT * FROM notes, and the search_path is “$user”, public. Your role is orbit and a schema named orbit exists with its own notes table. Which table is read?
Show answer
Answer: A. Postgres uses the first schema in search_path that contains a matching object. “$user” expands to the role name, so orbit.notes wins. Write public.notes to be explicit.
Exercise
Which ALTER TABLE is expected to finish instantly on a table with 500 million rows?
Show answer
Answer: B. Since PostgreSQL 11, a new column with a constant default is stored in the catalog and not written into every row. Type changes and VACUUM FULL rewrite the table.
Key takeaways
- Create a dedicated owner role and database per application, and set useful defaults such as the time zone on the database.
- Schemas are namespaces;
search_pathdecides where unqualified names are found. - Identity columns, primary keys,
NOT NULL,UNIQUE, defaults and foreign keys are declared with the table and enforced by the server. \d tableandinformation_schema.columnsshow a table’s structure.ALTER TABLEchanges tables in place; adding columns is cheap, type changes rewrite the table.
FAQ
Should I use one database per application or one schema per application?
One database per application is the usual choice: it is simpler to back up, restore, move and secure. Schemas are best for organising objects inside one application, or for multi-tenant designs where tenants share most structure.
Why bigint for ids instead of integer?
integer stops at about 2.1 billion. Running out on a busy table means a painful type change under pressure. bigint costs four more bytes per row and will never run out in practice.
What is the difference between GENERATED ALWAYS and GENERATED BY DEFAULT?
With ALWAYS, an INSERT that supplies its own id fails unless it says OVERRIDING SYSTEM VALUE, which prevents accidental collisions. BY DEFAULT accepts supplied ids silently. Prefer ALWAYS.
What about the old serial type?
serial creates an integer column with a sequence default. It still works, but identity columns are the SQL-standard replacement: they tie the sequence to the column, copy correctly with CREATE TABLE ... LIKE, and are easier to manage with permissions.
Can I rename a table that applications are using?
Yes, ALTER TABLE notes RENAME TO notes_v2; is instant, but every query using the old name breaks immediately. For a gradual move, rename the table and create a view with the old name that selects from it.