Postgres in depth · Chapter 2 of 65
A home for LCO's data
By the end of this chapter you can say where a table lives in Postgres (cluster, database, schema), create databases and schemas, choose where unqualified names are looked up, and name things so Postgres keeps the name you typed. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Day one: an empty server
- 4 tasks
- PostgreSQL
- Free chapter
Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.
Why you need this
Priya has a plan for LCO's data: the course catalogue in one place, payments in another, and a separate corner for the rows rescued from the old spreadsheet, so a messy import can never be mistaken for real data. "Before we create a single table," she says, "decide where it lives."
In chapter 1 you only asked the server questions. A SELECT can read a setting, but it cannot make a place to keep anything. You need the commands that create databases and schemas, and you need to know how Postgres finds a table when you type only its name.
The lesson
Cluster, database, schema, table
One running Postgres server manages a cluster: a set of databases that share one data folder, one set of roles (users) and one port. Inside it:
- a database is a separate world: a connection is always to exactly one database, and a query cannot read another database's tables;
- a schema is a folder inside a database that holds tables (and later views, functions and types);
- a table holds the rows.
So the full name of a table is database.schema.table, and you normally write only schema.table or just table. The catalog pg_database lists the cluster's databases:
SELECT datname, datistemplate FROM pg_database;
datname │ datistemplate
───────────┼───────────────
postgres │ f
template1 │ t
template0 │ t
lco_blank │ t
lco_main │ t
lco_edge │ t
lco │ f
(7 rows)
lco is yours. postgres is an empty database for tools to connect to. The others are templates, which the next section explains. In psql, \l shows the same list with more detail. Another column, datallowconn, says whether anyone may connect to a database at all.
CREATE DATABASE and templates
CREATE DATABASE lco_practice;
CREATE DATABASE
Postgres does not build a new database from nothing: it copies a template, template1 unless you say TEMPLATE name. Whatever is in template1 (tables, extensions) appears in every new database. template0 is a pristine copy nobody may connect to, kept for emergencies. The lab's Checks use the same trick: every Check runs in CREATE DATABASE chk_... TEMPLATE lco_blank, a fresh copy, which is why they never see your work.
Two traps:
- A template cannot be copied while someone is connected to it:
ERROR: source database "lco" is being accessed by other users
DETAIL: There is 1 other session using the database.
- Database names belong to the whole cluster, like roles. Two people cannot both create
lco_practice:
ERROR: database "lco_practice" already exists
To move into another database, psql reconnects with \c:
lco=> \c lco_practice
You are now connected to database "lco_practice" as user "app".
And from lco you cannot reach into it. Postgres refuses a three-part name (SELECT * means every column; chapter 5 covers it):
SELECT * FROM lco_practice.public.notes;
ERROR: cross-database references are not implemented: "lco_practice.public.notes"
That is the reason LCO will use one database with several schemas: one query can then read the catalogue and the payments together.
Schemas
Every new database has one schema, public. CREATE SCHEMA adds more, and psql's \dn lists them:
CREATE SCHEMA drafts;
lco_practice=> \dn
List of schemas
Name │ Owner
────────┼───────────────────
drafts │ app
public │ pg_database_owner
(2 rows)
A qualified name says which schema: drafts.notes is the table notes in drafts. Two schemas may hold tables with the same name. (Chapter 3 explains CREATE TABLE; here it only makes something to find.)
CREATE TABLE drafts.notes (body text);
CREATE TABLE notes (body text);
lco_practice=> \dt *.notes
List of relations
Schema │ Name │ Type │ Owner
────────┼───────┼───────┼───────
drafts │ notes │ table │ app
public │ notes │ table │ app
(2 rows)
The second notes had no schema in its name, yet it landed in public. Why there?
search_path
search_path is a setting: the list of schemas Postgres searches, in order, for a name without a schema. The first schema in the list that exists is also where new tables go.
SHOW search_path;
search_path
─────────────────
"$user", public
(1 row)
"$user" means a schema named after your role (app); there is none, so it is skipped and public wins. SET changes the setting for your session only (until you disconnect), and current_schemas(false) returns the list of schemas that actually exist, in order:
SET search_path = drafts, public;
SELECT current_schemas(false), current_schema();
current_schemas │ current_schema
─────────────────┼────────────────
{drafts,public} │ drafts
(1 row)
Now a new table without a schema goes to drafts, and a bare name is looked up in drafts first. CREATE TABLE ideas (body text); makes drafts.ideas. Set the path back to public alone and that table seems to vanish:
SET search_path = public;
SELECT * FROM ideas;
ERROR: relation "ideas" does not exist
It still exists; Postgres just no longer looks in its schema.
Identifiers and quoting
A name of a database, schema, table or column is an identifier. Postgres folds unquoted identifiers to lowercase, so CREATE SCHEMA Marketing creates marketing, and a second CREATE SCHEMA marketing fails:
ERROR: schema "marketing" already exists
Double quotes keep a name exactly as written: capitals, spaces, even keywords. Without them, a space ends the name:
CREATE SCHEMA Sales Team;
ERROR: syntax error at or near "Team"
CREATE SCHEMA "Sales Team" works, but now every use must be quoted the same way: "sales team".leads fails with relation "sales team.leads" does not exist. That is why the habit is lowercase names with underscores, which never need quotes.
Your turn
Your four tasks are at the top, one file each. Task 1 is a query, not a psql command: \l works only in psql, and the Check runs SQL. The interview task asks for names with capitals and a space: think about which quotes keep a name exactly as typed.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
templates.sql: the server's template databases
Every new database is a copy of a template. List the databases on this server that are marked as templates: one row each.
Return two columns: datname (the database's name) and datallowconn (whether anyone may connect to it). Any order.
Careful: write a query, not the psql command that lists databases. The Check runs SQL, and psql's backslash commands are not SQL.
- Task 2
schemas.sql: LCO's schemas
Priya wants LCO's tables split into two areas inside one database: one schema called catalog (courses, lessons) and one called billing (orders, payments).
Afterwards the database has exactly these two new schemas besides public, with these lowercase names. No tables yet.
- Task 3
search-path.sql: look in staging first
Rows from the old spreadsheet will be loaded into a schema called staging. Create that schema, then set this session's search path so that a name without a schema is looked up in staging first and then in public.
Afterwards, in the same session, the search path finds staging then public (in that order), so a new table created without a schema lands in staging.
Careful: the Check runs your file in a fresh copy of the database, so the file must create the schema itself.
- Task 4 · interview question
Interview: quoting.sql, names that keep their capitals
The import tool expects two schemas whose names are exactly Reports (capital R) and Old Spreadsheet (capital O and S, with a space). Create them.
Afterwards the database has exactly these two new schemas besides public, with exactly these names.
Careful: Postgres changes unquoted names to lowercase, and a space ends an unquoted name.
What you will learn
- Cluster, database, schema, table: the four levels, and what each one is for.
- CREATE DATABASE and templates: a new database is a copy of another.
- CREATE SCHEMA: folders inside one database, and names like drafts.notes.
- search_path: the list of schemas Postgres searches for a bare name.
- Identifiers and quoting: why Marketing becomes marketing.
More in Day one: an empty server
Build "A home for LCO's data" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
Free with a Google or GitHub sign-in, like the first 2 chapters of every lab.