Postgres in depth · Chapter 1 of 65

Welcome to LCO

By the end of this chapter you can connect to Postgres with psql, find your way around it, and ask the server your first questions in SQL. 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

LCO (LearnCodeOnline) teaches coding courses online. Until launch day, everything lived in one shared spreadsheet: students, courses, payments. Then 3,000 students signed up in a day. Two people edited the same rows at once and overwrote each other, a sorted column scrambled the payments, and nobody could answer Kabir's simple question: "how many students paid?"

A spreadsheet is a file for one person at a time. LCO needs a database: a server that many people and programs use at once, that refuses data breaking its rules, and that answers questions in a language made for it. You are LCO's new database engineer, and today you get an empty PostgreSQL server.

The lesson

Client and server

Postgres is a server: a program that runs all the time, keeps the data on disk and waits for clients to connect. psql (in your terminal), the LCO website and Arjun's analytics tools are all clients. They send SQL as text; the server runs it and sends rows back.

The server is not one process but a family. A process called the postmaster listens for connections, and for every connection it starts a new process, a client backend, that serves that one client until it disconnects. Helpers run beside them: the checkpointer and the walwriter write to disk, autovacuum cleans up.

A process per connection is safe (one crashing backend cannot corrupt another's memory) but costly: each one takes memory, so a server allows a limited number of connections. That is why real apps share a few connections through a pool, which a later chapter builds.

psql

Type psql in the terminal. It connects to your own database, lco, as the role app, and shows a prompt:

lco=>

Two kinds of input go there:

  • Backslash commands are answered by psql itself: \l lists the databases, \dt the tables, \d name describes one, \conninfo says where you are, \? lists them all, \q quits.
  • SQL is sent to the server, and only when it ends with ;. Press Enter without one and psql waits for more: the prompt turns into lco->. Type ; and press Enter to send it.
lco=> \conninfo
You are connected to database "lco" as user "app" via socket in "/tmp" at port "5432".
lco=> \dt
Did not find any relations.

No tables yet: LCO's database really is empty. You will build it chapter by chapter. In the editor, each task is a .sql file; lab run file.sql runs one against your database.

Your first SELECT

SELECT asks the server for values. It does not need a table:

SELECT 2 + 2;
 ?column?
──────────
        4
(1 row)

The answer is always a table of rows and columns, here one row and one column. Postgres could not guess a name for 2 + 2, so it called the column ?column?. AS gives it a name, and commas separate columns:

SELECT 499 * 2 AS two_courses, 'Hello' AS greeting;
 two_courses │ greeting
─────────────┼──────────
         998 │ Hello
(1 row)
  • * multiplies, + and - add and subtract. / divides, with a surprise: two whole numbers give a whole number, so 10 / 4 is 2 (chapter 16 explains why).
  • Text goes in single quotes. Double quotes mean a name (of a column or a table), so SELECT "Welcome to LCO"; fails:
ERROR:  column "Welcome to LCO" does not exist
  • Keywords like SELECT and AS are not case-sensitive; capitals are a habit that makes SQL easier to read.

Functions: the server answers

A function is a named calculation the server does for you, called with brackets: version(), now(), pg_backend_pid() (the process serving you). A few from the SQL standard take no brackets: current_user is the role you are logged in as.

SELECT pg_backend_pid();
 pg_backend_pid
────────────────
             83
(1 row)

Open a second psql and ask again: a different number, because it is a different process. A misspelt name fails the whole query:

ERROR:  function curent_database() does not exist

Settings

The server has hundreds of settings that control how it runs. SHOW reads one:

SHOW shared_buffers;
 shared_buffers
────────────────
 64MB
(1 row)

SHOW is a command of its own: it reads one setting and cannot be part of a SELECT. When you need a setting inside a query, the function current_setting('name') returns the same value as text:

SELECT current_setting('datestyle') AS datestyle;
 datestyle
───────────
 ISO, MDY
(1 row)

Who is connected?

pg_stat_activity is a view: something you query like a table, which Postgres fills in live. It has one row per server process:

SELECT pid, backend_type FROM pg_stat_activity;
 pid │         backend_type
─────┼──────────────────────────────
  15 │ checkpointer
  16 │ background writer
  18 │ walwriter
  19 │ autovacuum launcher
  20 │ logical replication launcher
 153 │ client backend
(6 rows)

The client backend is you; the rest are the helpers from the animation. Its other columns include datname (the database a connection is in, empty for helpers), usename (its role) and state (active while it runs a query, idle while it waits).

Your turn

Your four tasks are at the top, one file each. Task 2 has a trap: your terminal is in lco, but the Check runs your file in a fresh copy of the database with another name, so ask Postgres instead of typing a name. The interview question asks for the connections to the current database only.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    launch.sql: the launch numbers

    LCO's first bootcamp opens with 3 batches of 40 seats each. Write one query that returns one row with the numbers.

    Return three columns: batches (3), seats_per_batch (40) and total_seats (the two multiplied, worked out by Postgres).

  2. Task 2

    whoami.sql: which database, which role

    Return one row saying which database this query runs in and which role (user) it runs as.

    Return two columns: database_name and role_name. Ask Postgres for both: the Check runs your file in a different database than your terminal does, so a name typed by hand is wrong there.

  3. Task 3

    settings.sql: three server settings in one row

    Priya wants the server's version, its time zone and how many connections it allows, in one row.

    Return three columns: server_version, timezone and max_connections, each the value of the setting with that name (as text, the way Postgres stores settings).

  4. Task 4 · interview question

    Interview: connections.sql, the connections to this database

    Every connection to Postgres has its own server process. List the connections to the database this query runs in: one row per connection.

    Return three columns: pid (the server process's id), usename (the role it logged in as) and state. Any order.

    Careful: the server has connections to other databases, and background processes with no database at all. Keep only the ones whose database is the current one.

What you will learn

  • Client and server: who talks to whom, and why each connection gets its own process.
  • psql: Postgres's terminal client, its backslash commands, and how SQL ends.
  • SELECT: asking for values, doing arithmetic, naming columns.
  • Functions, settings and pg_stat_activity: asking the server about itself.

More in Day one: an empty server

Build "Welcome to LCO" 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.