Full-Stack Development · Tutorial 10 of 11
PostgreSQL Reference: tables, keys and the queries you will reuse
By the FUTEAC Team · 18 November 2026 · 12 min read · Beginner
In short
PostgreSQL stores your app's data in tables made of rows and columns. This reference gives you the statements you will reuse: CREATE TABLE with keys and constraints, INSERT, UPDATE and DELETE with RETURNING, and the SELECT queries for filtering, sorting, joining and counting. Every statement here was run against a small books database, and the output shown is real.
- A table is a list with rules. Columns set the shape, constraints set the rules, and PostgreSQL refuses data that breaks them.
- Keys are relationships, not decoration. A primary key identifies each row, a foreign key ties a row to a row in another table.
- RETURNING saves a round trip. INSERT, UPDATE and DELETE can hand back the rows they touched, so your API does not need a second query to see what changed.
- Write the WHERE first. Every UPDATE and DELETE in this reference carries a WHERE clause, and the wrong turn below shows what happens without one.
- This reference feeds the Node & Express API Foundations course (/course/node-express-api-foundations), module "Working with a Database", and the PostgreSQL (/skill/postgresql) skill.
Before you start
1. The two tables used in every example
Every example on this page runs against the same small bookshop: authors and their books. One author has many books, so books.author_id points at authors.id. Think of it like a filing cabinet: one drawer per author, and every book card filed under its author's name.
CREATE TABLE authors (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(200) NOT NULL,
country VARCHAR(100),
born_year INT CHECK (born_year > 0)
);
CREATE TABLE books (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
author_id INT NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
title VARCHAR(200) NOT NULL,
isbn VARCHAR(13) UNIQUE,
price NUMERIC(10, 2) NOT NULL DEFAULT 0,
published_year INT,
in_stock BOOLEAN NOT NULL DEFAULT true
);The data used for every result below:
INSERT INTO authors (name, country, born_year) VALUES
('J.R.R. Tolkien', 'United Kingdom', 1892),
('Frank Herbert', 'United States', 1920),
('William Gibson', 'United States', 1948);
INSERT INTO books (author_id, title, isbn, price, published_year, in_stock) VALUES
(1, 'The Hobbit', '9780261102217', 12.99, 1937, true),
(1, 'The Lord of the Rings', '9780261102699', 24.99, 1954, true),
(2, 'Dune', '9780441172719', 14.99, 1965, true),
(3, 'Neuromancer', '9780441569595', 9.99, 1984, false);2. How to create a table: types and constraints
Pick the type that matches the data, and let the constraint do the checking so your code does not have to.
| Type | Use it for | Example |
|---|---|---|
INT | Whole numbers: years, counts, ids | published_year INT |
NUMERIC(10, 2) | Money and exact decimals: 10 digits, 2 after the point | price NUMERIC(10, 2) |
VARCHAR(200) | Short text with a maximum length | title VARCHAR(200) |
TEXT | Long text with no length limit | description TEXT |
BOOLEAN | True or false | in_stock BOOLEAN |
TIMESTAMPTZ | A date and time with timezone | created_at TIMESTAMPTZ DEFAULT now() |
| Constraint | What it does | Example |
|---|---|---|
PRIMARY KEY | Identifies each row, unique and never null | id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY |
NOT NULL | The column must have a value | title VARCHAR(200) NOT NULL |
UNIQUE | No two rows may share this value | isbn VARCHAR(13) UNIQUE |
DEFAULT | Value used when none is given | price NUMERIC(10, 2) NOT NULL DEFAULT 0 |
CHECK | A rule the value must pass | born_year INT CHECK (born_year > 0) |
REFERENCES | Foreign key, see section 3 | author_id INT REFERENCES authors(id) |
Two details worth knowing. GENERATED ALWAYS AS IDENTITY is the modern way to get an auto-numbered id; older tutorials write SERIAL for the same job, and you will still see it in other people's code. And VARCHAR(200) versus TEXT: for a title, either works, and the length limit is the only real difference.
3. Keys: what each one does
| Key | Meaning | What happens if you break it |
|---|---|---|
Primary key (id) | The row's identity. PostgreSQL numbers it for you. | You cannot insert two rows with the same id, and you cannot leave it empty. |
Foreign key (author_id) | This book belongs to that author. The author must exist first. | Inserting a book with author_id 99 fails, because author 99 does not exist. |
Unique (isbn) | No two books share one ISBN. | A second insert with the same ISBN is refused. |
The foreign key also decides what happens on delete. Our books table says ON DELETE CASCADE, so deleting an author deletes their books too. We ran it: deleting William Gibson left 3 books, down from 4, because Neuromancer went with him. That is the behaviour you want for owned records like books, and the behaviour you do not want for records other things still point at, like a customer with past orders. Choose the rule per table, not once for the whole database.
4. Writing data: INSERT, UPDATE, DELETE
Insert one row, or several at once. Leave the id out and let PostgreSQL number it.
INSERT INTO books (author_id, title, price, published_year)
VALUES (2, 'Dune Messiah', 11.99, 1969);RETURNING hands back the row that was just written, including the id PostgreSQL assigned. Run it and you get the new id without a second query.
INSERT INTO books (author_id, title, price, published_year)
VALUES (2, 'Dune Messiah', 11.99, 1969)
RETURNING id;Output: id: 5.
Update one value, and say which rows with WHERE. This changes the price of Dune only.
UPDATE books SET price = 13.99 WHERE title = 'Dune'
RETURNING id, title, price;Output: id: 3, title: Dune, price: 13.99.
Delete the same way, always with WHERE.
DELETE FROM books WHERE title = 'Dune Messiah';One row deleted, and the count afterwards is 4 books again. The RETURNING clause works on DELETE too: DELETE FROM books WHERE id = 5 RETURNING id, title; tells you exactly which row is gone.
5. Reading data: the queries you will reuse
These are the SELECT patterns a real app repeats. Each one ran against the books data above. The results assume the original four books from section 1, before the edits in section 4.
| What you want | Statement | Result on our data |
|---|---|---|
| All books by one author | SELECT b.title, b.price FROM books b JOIN authors a ON b.author_id = a.id WHERE a.name = 'J.R.R. Tolkien' ORDER BY b.published_year; | The Hobbit 12.99; The Lord of the Rings 24.99 |
| How many books each author has | SELECT a.name, COUNT(b.id) AS book_count FROM authors a LEFT JOIN books b ON b.author_id = a.id GROUP BY a.name ORDER BY book_count DESC; | Tolkien 2; Herbert 1; Gibson 1 |
| Average price of all books | SELECT AVG(price) AS avg_price FROM books; | 15.74 |
| Cheapest books in stock, two only | SELECT title, price FROM books WHERE in_stock = true ORDER BY price ASC LIMIT 2; | The Hobbit 12.99; Dune 14.99 |
| Search titles without caring about case | SELECT title FROM books WHERE title ILIKE '%dune%'; | Dune |
| Books published after 1950 | SELECT title, published_year FROM books WHERE published_year > 1950 ORDER BY published_year; | The Lord of the Rings 1954; Dune 1965; Neuromancer 1984 |
Three notes on the table. LEFT JOIN in the count query keeps authors with zero books in the result; an INNER JOIN would drop them, and the count would silently lie. ILIKE is PostgreSQL's case-insensitive LIKE; plain LIKE is case-sensitive, so '%Dune%' would miss 'dune'. And LIMIT without ORDER BY returns an arbitrary two rows, so always pair them.
6. Changing a table later
Tables are not frozen. Add a column, rename one, drop one, and add an index when a query gets slow.
ALTER TABLE books ADD COLUMN pages INT;
ALTER TABLE books RENAME COLUMN published_year TO year_published;
ALTER TABLE books DROP COLUMN pages;
CREATE INDEX idx_books_author_id ON books(author_id);All four ran without error. The index belongs on author_id because nearly every query in section 5 joins or filters on it; an index on a column you never filter by is dead weight. Add indexes when a real query is slow, not in advance.
7. The wrong turn: UPDATE without WHERE
This is the mistake every beginner makes once, and it is the reason section 4 repeats the WHERE rule. Run this on a scratch copy of the books table:
UPDATE books SET price = 9.99;No error. No warning. PostgreSQL changes all 4 rows, and every book now costs 9.99: The Hobbit 9.99, The Lord of the Rings 9.99, Dune 9.99, Neuromancer 9.99. The statement did exactly what it says, and what it says is "every row". The fix is a habit, not a feature: write the WHERE clause first, then the SET clause, and read the statement back before pressing enter. On a table you care about, run the same condition as a SELECT first (SELECT * FROM books WHERE title = 'Dune';) and check it returns only the rows you mean to change.
8. Calling PostgreSQL from Node
The course lesson "Connecting Express to PostgreSQL" wires the connection up; this is the query shape it uses, and the one rule that matters. Never build SQL by joining strings together. Pass values as parameters, $1, $2, and let the driver send them separately from the SQL.
const result = await pool.query(
"SELECT title, price FROM books WHERE author_id = $1 ORDER BY price ASC",
[1]
);The $1 is a placeholder, and [1] is the value that fills it. A title containing a quote or a semicolon stays data, never becomes SQL. That is the whole defence against SQL injection, and it is one line.
Try it yourself
Continue in the free course: Node & Express API Foundations →
Common mistakes
- UPDATE or DELETE with no WHERE. See section 7. Four rows changed when one was meant.
- Storing passwords as plain text. Hash them first; the Authentication Basics tutorial (item 32) shows how, and the course lesson "Register and Login: Hashing Passwords Safely" is the full version.
- Single quotes for values, double quotes for names.
'Dune'is a string;"Dune"is a column or table name. Mixing them up is the most common syntax error in this reference. - SELECT \* in application code. It returns columns you do not need, and breaks the day a column is added or renamed. Name the columns, as every query in section 5 does.
- No index on the foreign key. Joins on
author_idscan the whole table without one. Add it when the table grows, as in section 6.
Frequently asked questions
Do I need to memorise all of this?
No. Memorise the shape: CREATE TABLE once, INSERT, UPDATE and DELETE with WHERE, and SELECT with WHERE, ORDER BY and LIMIT. Keep this page open for the rest, and copy the pattern that matches the job.
PostgreSQL or MySQL, which should a beginner pick?
Either teaches the same SQL. This roadmap picks PostgreSQL, so the course, the skill page and this reference all line up on one database.
What is the difference between VARCHAR and TEXT?
Only the length limit. VARCHAR(200) refuses a 201-character title; TEXT accepts anything. Use VARCHAR when a limit is part of the rule, TEXT when it is not.
Can I change a table after it has data in it?
Yes, with ALTER TABLE, as in section 6. Adding a column is safe; dropping a column deletes its data, so check first. Renaming a column breaks any code that uses the old name, so rename the code in the same change.
Why does my query return rows in a different order each time?
Because you did not ask for an order. Without ORDER BY, PostgreSQL returns rows in whatever order is fastest, and that changes. Add ORDER BY whenever order matters.
Follow the Full-Stack Developer Roadmap
Build complete web applications, frontend, backend APIs, databases and deployment. Free, step by step, at your pace.
View the Roadmap