A PostgreSQL install is two programs. The server (postgres) runs in the background, owns the data directory, and listens for connections on port 5432. A client connects to it, sends SQL, and shows the results. psql is the client that ships with PostgreSQL; your Python, Go, or Node program is a client too, through a driver library. This split is the big difference from SQLite: with SQLite your program opens a file, but with PostgreSQL it opens a connection, and many connections can be open at once.

Every connection needs four things: the host (which machine), the port, the role (which PostgreSQL user you are), and the database. psql fills in defaults for all four: the local machine, port 5432, a role with your operating-system login name, and a database with the same name as the role. That's why a bare psql works once you've created a role and database named after yourself in the Installation tab, and why you can override any of them with -h, -p, -U, and -d. The prompt shows which database you're in, followed by => for a normal role or =# for a superuser.

Server

The background program that owns the data and answers queries; clients connect to it.

Client

Any program that connects to the server and sends it SQL, such as psql.

Role

PostgreSQL's name for a user account; roles are separate from your computer's accounts.

You can type two kinds of input at the psql prompt. SQL statements such as SELECT and CREATE TABLE go to the server. They end with a semicolon, and they're the same SQL your programs will send. Meta-commands start with a backslash and are handled by psql itself; the server never sees them. They take up one line and need no semicolon. Behind the scenes, many of them run ordinary SQL against PostgreSQL's built-in system catalogs, the tables where the server records its own tables, columns, and roles.

The names follow a pattern: \d means describe, and the letter after it says what to describe. \dt lists tables, \dv views, \di indexes, \dn schemas, and \du roles; \d users describes one table in full. \l lists databases and \c connects to a different one. Two display toggles are worth learning early. \x switches to expanded display, printing each row as a list of column | value lines, which is far easier to read for wide rows. \timing prints how long each query took. \? lists every meta-command, and \h gives the syntax of any SQL statement, such as \h CREATE TABLE.

Meta-command

A backslash command handled by psql itself; it is not SQL and needs no semicolon.

System catalog

PostgreSQL's built-in tables that describe the database itself.

Expanded display

The \x mode that prints each row as a vertical list of column/value pairs.

A PostgreSQL server holds many databases, and each one is completely separate: a query in one can't see tables in another. It's normal to make one database per project. Inside a database, tables live in schemas, which are named folders; every database starts with one called public, which is where your tables go unless you say otherwise. Since PostgreSQL 15, ordinary roles may not create tables in the public schema of a database they don't own. Creating your own database, as you do here, makes you its owner and avoids that error.

A table's columns each have a data type, and PostgreSQL enforces it strictly: an integer column refuses 'abc', and a date column refuses '2026-02-30'. The everyday types are integer (and bigint for very large numbers), numeric(10,2) for exact decimals such as money, text for strings of any length, boolean, date, and timestamptz for a moment in time. GENERATED ALWAYS AS IDENTITY makes the server number the rows itself; it's the modern replacement for the older serial. Constraints add rules: PRIMARY KEY identifies each row, NOT NULL requires a value, UNIQUE forbids duplicates, CHECK enforces any condition you write, and DEFAULT fills in a value you leave out.

Database

A separate container of schemas and tables on the server; one per project is typical.

Schema

A named folder of tables inside a database; the default one is called public.

Identity column

A column whose values the server generates automatically: 1, 2, 3, and so on.

INSERT INTO names the table, lists the columns you're filling, and gives the values in the same order after VALUES. Columns you leave out get their DEFAULT, or NULL if they have none, and an identity column is numbered for you. One statement can insert many rows: list several parenthesised groups separated by commas. psql reports what happened with a command tag such as INSERT 0 3; the 3 is the number of rows, and the 0 is a leftover from older versions that you can ignore.

RETURNING is a PostgreSQL feature worth using from day one. It makes an INSERT, UPDATE, or DELETE return rows, just like a SELECT, so you get the generated id back without a second query. Two rules about quoting: text values go in single quotes, and an apostrophe inside one is written twice, as in 'O''Brien'. Double quotes are for the names of tables and columns. When a program inserts user input, it must pass values as parameters and never paste them into the SQL text, or a crafted value can rewrite the query. That attack is called SQL injection. When a row breaks a constraint, the server rejects it and names the constraint in the error.

Command tag

The short status psql prints after a statement, such as INSERT 0 3 or UPDATE 1.

RETURNING

A clause that makes INSERT, UPDATE, or DELETE return the rows it affected.

SQL injection

An attack where untrusted input is pasted into SQL text; prevented by using parameters.

SELECT is the statement you'll write most, and its answer is always a small table called a result set. A query is made of clauses, each with one job. SELECT lists the columns (* means all of them), FROM names the table, WHERE keeps only the rows where a condition is true, ORDER BY sorts (add DESC for descending), and LIMIT caps the number of rows. OFFSET skips rows first, which is how page 2 of a list is built. AS renames a column in the output; the new name is called an alias.

SQL is declarative: you describe the result you want and the server's query planner decides how to fetch it. Logically, the clauses run in a different order from how you write them: FROM, then WHERE, then SELECT, then ORDER BY, then LIMIT. That's why an alias defined in SELECT can be used in ORDER BY but not in WHERE. Rows have no guaranteed order unless you ask for one. Without ORDER BY, PostgreSQL returns rows in whatever order is cheapest, and that order can change after an update. count(*) counts rows; it's the first of the aggregate functions covered at the Intermediate level.

Result set

The table of rows a query returns.

Clause

One part of a statement with a single job, such as WHERE or ORDER BY.

Query planner

The part of the server that decides how to execute a query efficiently.

UPDATE names a table, sets one or more columns with SET, and changes every row that matches its WHERE clause. DELETE FROM removes every matching row. Neither asks for confirmation, and without a WHERE clause, both affect every row in the table. The command tag tells you how many rows were hit: UPDATE 1 is what you expected, and UPDATE 0 means your condition matched nothing, which is usually a typo. Adding RETURNING * shows you exactly which rows changed.

There are three ways to remove data, and they do different things. DELETE removes rows and can be filtered with WHERE. TRUNCATE empties a whole table at once; it's much faster on big tables and resets nothing unless you add RESTART IDENTITY. DROP TABLE removes the table itself: its structure, data, indexes, and all. The safe habit is to write the WHERE clause first, run it as a SELECT to see which rows it matches, and only then turn it into an UPDATE or DELETE. Transactions, in the Advanced section, give you an undo button on top of that.

WHERE clause

The condition that chooses which rows a statement reads, changes, or deletes.

TRUNCATE

Empties a table in one fast operation, without scanning its rows.

DROP TABLE

Removes a table completely: structure, data, and indexes.

A WHERE condition is a boolean expression: for each row it works out to true or false, and only rows where it's true are kept. AND needs both sides true and OR needs either. AND binds tighter than OR, the way multiplication binds tighter than addition, so a OR b AND c means a OR (b AND c). Use parentheses whenever you mix them. BETWEEN x AND y is an inclusive range, and IN (...) matches any value in a list, which is tidier than a chain of ORs.

For text patterns, % matches any run of characters and _ matches exactly one. Here PostgreSQL differs from SQLite and MySQL: LIKE is case-sensitive, so 'alice' LIKE 'A%' is false. Use ILIKE for case-insensitive matching. NULL means unknown, and any comparison with an unknown value is itself unknown, so email = NULL is never true, even for rows with no email. SQL calls this three-valued logic. Test for missing values with IS NULL and IS NOT NULL, and use IS DISTINCT FROM when you want a comparison that treats two NULLs as equal.

Boolean expression

A condition that is true, false, or unknown for each row.

ILIKE

PostgreSQL's case-insensitive version of LIKE.

Three-valued logic

SQL's true / false / unknown; any comparison with NULL is unknown.

Good table design stores each fact once. Rather than copying a customer's name onto every order, an orders table stores the customer's id in a user_id column. Declaring that column REFERENCES users makes it a foreign key: the server refuses any order whose user_id doesn't exist in users, and refuses to delete a user who still has orders. That guarantee is called referential integrity. ON DELETE CASCADE changes the second rule, so deleting a user deletes their orders too. The alternative, ON DELETE SET NULL, keeps the orders and blanks the link.

A join combines rows from two tables where a condition matches, usually a foreign key equal to a primary key. INNER JOIN (or just JOIN) returns only the pairs that match, so a user with no orders disappears from the result. LEFT JOIN keeps every row from the left-hand table and fills the right-hand columns with NULL where there's no match, which is how you find users who have never ordered. Short table aliases like u and o keep joins readable, and prefixing columns with them (u.name) removes any doubt about which table a column comes from.

Foreign key

A column whose values must exist as a key in another table.

Referential integrity

The guarantee that every foreign key points at a row that really exists.

LEFT JOIN

A join that keeps every left-hand row, with NULLs where the right side has no match.

Aggregate functions take a whole set of rows and return one value: count counts, sum adds, avg averages, and min and max find the extremes. They skip NULLs, which is why count(*) (rows) and count(email) (rows that have an email) can differ. round(value, 2) tidies an average to two decimal places. Run an aggregate on its own and you get a single summary row for the whole table.

GROUP BY splits the rows into groups that share a value, such as one group per order status, and runs the aggregates once per group. WHERE filters individual rows before grouping, and HAVING filters whole groups after the aggregates are calculated, which is why HAVING sum(amount) > 100 works and WHERE sum(amount) > 100 is an error. Every column in the SELECT list must either appear in GROUP BY or sit inside an aggregate, and PostgreSQL enforces this where SQLite lets it slide. PostgreSQL also has a tidy FILTER clause for counting only some rows inside an aggregate.

Aggregate function

A function that summarises many rows into one value, such as sum or count.

GROUP BY

Splits rows into groups that share a value, giving one result row per group.

HAVING

Filters groups after aggregation; WHERE filters rows before it.

Real schemas change over time, and ALTER TABLE makes those changes in place without losing data. ADD COLUMN adds a column; existing rows get the column's default, or NULL. RENAME COLUMN and RENAME TO change names. DROP COLUMN removes a column and its data. ALTER COLUMN ... TYPE converts a column to a new type, and when PostgreSQL can't convert automatically, a USING expression tells it how. SET DEFAULT, SET NOT NULL, and ADD CONSTRAINT tighten the rules after the fact, and the server checks the existing rows first: if any break the new rule, the change is refused.

Unlike many databases, PostgreSQL has transactional DDL. DDL means the statements that define structure, such as CREATE, ALTER, and DROP, and in PostgreSQL they can run inside a transaction and be rolled back like any UPDATE. That makes schema changes much safer: wrap a risky migration in BEGIN and check the result before you COMMIT. On large tables, some changes rewrite the whole table and lock it while they run, such as changing a column's type. Adding a nullable column, or one with a constant default, is instant.

ALTER TABLE

The statement that changes the structure of an existing table.

DDL

Data Definition Language: statements that define structure, such as CREATE and ALTER.

Transactional DDL

The ability to roll back structure changes, which PostgreSQL supports.

Functions transform values. For text: upper, lower, and length do what they say, || joins strings together, left(s, n) takes the first characters, and split_part cuts a string at a delimiter, which is handy for pulling the domain out of an email address. For numbers: round, ceil, floor, and abs. coalesce(a, b, ...) returns the first argument that isn't NULL, which is the standard way to show a fallback value. CASE WHEN ... THEN ... ELSE ... END is SQL's if/else: it picks a value row by row.

Dates and times are a PostgreSQL strength. current_date and now() give today and this moment. Subtracting two dates gives a number of days, and adding an interval such as interval '30 days' moves a date. age() gives the time between two dates in years, months, and days, extract(year FROM d) pulls out one part, and date_trunc('month', d) rounds down to the start of the month, which is the usual way to group sales by month. The :: operator casts a value to another type, for example '2025-01-31'::date or price::integer, and generate_series produces a run of numbers or dates, which is useful for test data.

Cast

Converting a value to another type, written value::type in PostgreSQL.

Interval

A length of time, such as interval '30 days', that can be added to dates.

coalesce

Returns its first argument that is not NULL.

Without an index, finding rows means a sequential scan: reading every row in the table and testing each one. That's fine for a few hundred rows and slow for ten million. An index is a separate, sorted structure (a B-tree by default) that maps column values to row locations, so the server can find matches the way you'd use the index at the back of a book. PostgreSQL creates one automatically for every PRIMARY KEY and UNIQUE constraint. You add others for columns you often search, join, or sort on. The price is disk space and slightly slower writes, because every insert and update must keep the index current.

EXPLAIN shows the query plan, the steps the planner chose, without running the query; EXPLAIN ANALYZE runs it and adds real timings. Read plans from the innermost line outward. Seq Scan means the whole table was read. Index Scan or Bitmap Index Scan means an index was used. The cost numbers are the planner's estimates in arbitrary units, and rows is how many rows it expects. The planner decides from statistics that ANALYZE gathers (autovacuum does this in the background), so on a tiny table it will often ignore your index, correctly, because reading a few pages is cheaper than using the index.

Sequential scan

Reading every row of a table to find the matches.

Index

A sorted lookup structure that lets the server find rows without reading them all.

Query plan

The steps the server will take to run a query, shown by EXPLAIN.

A view gives a name to a SELECT. Once CREATE VIEW customer_totals AS ... exists, you can query customer_totals like a table, filter it, and join it to other tables, but no data is copied: each time you select from it, PostgreSQL runs the underlying query again, so a view is always up to date. Views are useful for hiding complicated joins behind a simple name, and for giving a role access to some columns of a table without granting access to the whole table. CREATE OR REPLACE VIEW updates the definition.

A materialized view runs its query once and stores the result like a real table. Reading from it is as fast as reading a table, which makes it ideal for expensive reports, but it's a snapshot: it doesn't change when the underlying tables do. REFRESH MATERIALIZED VIEW runs the query again and replaces the stored rows. On a busy system, adding CONCURRENTLY lets people keep reading the old contents during the refresh; it requires a unique index on the materialized view. \dv lists views and \dm lists materialized views.

View

A named query that runs every time you select from it; it stores no data.

Materialized view

A query whose results are stored; it's fast to read but only as fresh as the last refresh.

REFRESH

Re-runs a materialized view's query and replaces its stored rows.

A subquery is a SELECT inside another statement. A scalar subquery returns one value and can go anywhere a value can, for example WHERE amount > (SELECT avg(amount) FROM orders). A subquery that returns a column can feed IN, and EXISTS (SELECT 1 ...) is true when the inner query returns any row at all. A correlated subquery refers to the outer row, as in users who have at least one order, and conceptually runs once per outer row, though the planner often rewrites it as a join.

A common table expression (CTE), written with WITH name AS (...), gives a subquery a name so you can build a query in readable steps and refer to each step more than once. Since PostgreSQL 12, simple CTEs are folded into the main query, so they cost nothing extra. WITH RECURSIVE lets a CTE refer to itself. It starts from a seed row and repeatedly adds new rows until none are produced, which is how you walk a tree such as an organisation chart or a category hierarchy, or generate a sequence.

Subquery

A query nested inside another statement.

Correlated subquery

A subquery that refers to columns of the outer query.

CTE

A named subquery written with WITH; WITH RECURSIVE can refer to itself.

A transaction makes several statements act as one. Moving money between two accounts is the classic example: take it out of one, put it into the other, and it must never be possible for only half of that to happen. BEGIN starts a transaction, and nothing you change is visible to anyone else until COMMIT. ROLLBACK throws all the changes away. Every statement you run outside BEGIN is its own small transaction that commits immediately; that's called autocommit. Transactions give four guarantees, known as ACID: atomic (all or nothing), consistent (constraints hold), isolated (other sessions don't see half-finished work), and durable (committed data survives a crash).

psql shows the transaction state in the prompt: => outside a transaction, =*> inside one, and =!> inside one that has hit an error. That last state surprises people. After any error, PostgreSQL refuses every further statement in the transaction until you ROLLBACK, so a script can't carry on as if nothing had happened. SAVEPOINT name marks a point inside a transaction, and ROLLBACK TO name undoes back to it without abandoning the whole transaction.

Transaction

A group of statements that commit together or not at all.

Autocommit

The default where each statement outside BEGIN is committed immediately.

ACID

Atomic, consistent, isolated, durable: the guarantees a transaction provides.

A role can log in, own objects, and hold privileges. Whoever creates a table owns it and can do anything with it; everyone else can do nothing until they're granted it. Creating roles needs the CREATEROLE attribute or a superuser, which is why the first command below runs as postgres. LOGIN lets a role connect, and PASSWORD sets its password. A role without LOGIN acts as a group: grant privileges to the group, then GRANT groupname TO person, and every member inherits them.

Privileges work at several levels, and a role needs all of them to read a table: CONNECT on the database, USAGE on the schema, and SELECT on the table itself. INSERT, UPDATE, and DELETE are granted separately, so a reporting role can read everything and change nothing. GRANT SELECT ON ALL TABLES IN SCHEMA public covers the tables that exist now, and ALTER DEFAULT PRIVILEGES covers tables created later. REVOKE takes privileges away. The principle of least privilege says each program should connect as a role with only the privileges it needs, never as a superuser.

Privilege

A permission such as SELECT or INSERT on a specific object.

Group role

A role without LOGIN used to bundle privileges for its members.

Least privilege

Giving each role only the access it actually needs.

An upsert inserts a row, or updates the existing one if it would break a unique constraint. PostgreSQL writes it as INSERT ... ON CONFLICT (column) DO UPDATE SET .... Inside the DO UPDATE, the special name EXCLUDED refers to the row you tried to insert, so SET age = EXCLUDED.age copies the new value over. ON CONFLICT DO NOTHING silently skips duplicates instead, which is handy when importing data that might already be there. The conflict target must match a real UNIQUE or PRIMARY KEY constraint.

Window functions compute a value for each row from a set of related rows, its window, without collapsing them the way GROUP BY does. The OVER (...) clause defines the window: PARTITION BY splits rows into groups and ORDER BY orders them within each group. row_number() numbers rows 1, 2, 3; rank() gives ties the same rank; sum(amount) OVER (ORDER BY ordered_at) is a running total; and lag() reads a value from the previous row, which is how you compute the change from one row to the next.

Upsert

Insert a row, or update the existing one if it conflicts: INSERT ... ON CONFLICT.

EXCLUDED

In ON CONFLICT DO UPDATE, the row that was proposed for insertion.

Window function

A function computed over related rows while keeping every row in the result.

Sometimes part of your data has no fixed shape: product attributes that differ from one product to the next, or settings that change every release. PostgreSQL's jsonb type stores a whole JSON document in one column. It's parsed and stored in a binary form, so it's validated on the way in (malformed JSON is refused) and fast to query. There's also a plain json type that keeps the exact text, but jsonb is the one to use.

The operators are short: -> gets a field as JSON, ->> gets it as text, and #>> follows a path. @> means contains: attrs @> '{"color": "black"}' finds every product whose attributes include that key and value. ? tests whether a key exists. jsonb_set returns a changed copy of a document, and || merges two. A GIN index on a jsonb column makes @> and ? searches fast even across millions of rows. A good rule of thumb: keep the fields you filter and join on in real columns, and use jsonb for the long tail of optional ones.

jsonb

A column type that stores validated JSON in a binary, queryable form.

Containment (@>)

Tests whether a JSON document contains the given keys and values.

GIN index

An index type suited to searching inside jsonb, arrays, and full text.

A logical backup is a file of SQL, or an archive, that can recreate a database. pg_dump shop > shop.sql writes plain SQL that you can read and replay with psql -f. pg_dump -Fc writes PostgreSQL's compressed custom format, which pg_restore can load selectively, for example one table, and in parallel. pg_dump takes a consistent snapshot while the database stays in use, so there's no need to stop anything. pg_dumpall also saves roles, which pg_dump doesn't. Test your backups by actually restoring them into a scratch database: a backup you've never restored is only a hope.

\copy is psql's bridge to spreadsheets. \copy users TO 'users.csv' CSV HEADER writes a CSV file on your machine, and \copy ... FROM reads one back into a table. There's also a server-side SQL command, COPY, which reads and writes files on the server's disk and needs special privileges. That difference trips people up, and \copy is almost always the one you want. Both report the number of rows moved, as in COPY 5. The column list is optional, and it lets you load a CSV whose columns are in a different order from the table's.

Logical backup

A dump of SQL or an archive that can recreate a database; made with pg_dump.

pg_restore

Loads a custom-format dump, optionally in parallel or one table at a time.

\copy

psql's command for moving table data to and from files on your own machine.