Recommended Free Tools
To get started with PostgreSQL, install a server using the instructions for your operating system or package, connect with a client such as psql, and use SQL to create a database, tables, and queries. This guide takes you from a first connection to joins, transactions, JSONB, and indexes. It targets PostgreSQL 18; installation details can differ across operating systems, packages, and vendor distributions.
How do I get started with PostgreSQL?
PostgreSQL is a relational database system: it stores information in tables, lets you describe relationships between those tables, and provides SQL to read and change the data. A PostgreSQL server runs the database service; a database is a named space for objects such as tables; a client sends commands to the server and displays results. psql is PostgreSQL’s interactive command-line client.
The official PostgreSQL 18 tutorial is designed as a hands-on introduction to PostgreSQL, relational concepts, and SQL. It assumes general computer familiarity, not prior Unix or programming experience, and explicitly does not aim to cover every subject in depth.
Installation and service startup depend on your operating system and how PostgreSQL was packaged. Follow the instructions for your specific package or vendor distribution rather than treating one installation command as universal. The PostgreSQL manual’s server setup and operation documentation is a starting point for installation and server-management topics. The documentation landing page linked here identifies version 18.6 and lists major versions 18, 17, 16, 15, and 14 as supported; check it for the current release and use the manual matching your installed major version.
#1 Best Overall
How do I create a database and connect to it?
After installing and starting the server according to your package’s instructions, open a terminal where psql is available. This example assumes you can connect to a database named postgres; the role and authentication method depend on your installation.
-
Connect to the server’s default administrative database:
psql -d postgres. If the connection needs a particular user or server address,psqlaccepts options such as-U usernameand-h hostname; use the values configured for your installation. -
At the
psqlprompt, create a database:CREATE DATABASE reading_room;. PostgreSQL should respond withCREATE DATABASE. -
Switch the same
psqlsession to it with the client commandconnect reading_room, also writtenc reading_room. Acommand is apsqlinstruction, not SQL; semicolons are used to end SQL statements, not these client commands.Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.
You can also create a database with the createdb utility if it is installed and configured, but using SQL here makes the distinction between server, database, and client visible. For application connections, credentials and connection details are usually supplied by the application or its environment; the command-line session is just one way to interact with the same server.
How do I create a table, add rows, and query them?
A table has named columns with data types. Start with a small catalog of authors and books so the examples can build on one another. Run these statements inside reading_room:
Rank #2
CREATE TABLE authors (
author_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE books (
book_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
author_id integer NOT NULL REFERENCES authors (author_id),
published_year integer,
price numeric(6, 2) NOT NULL CHECK (price >= 0)
);
INSERT INTO authors (name)
VALUES ('Ursula K. Le Guin'), ('Octavia E. Butler');
INSERT INTO books (title, author_id, published_year, price)
VALUES
('A Wizard of Earthsea', 1, 1968, 12.99),
('The Left Hand of Darkness', 1, 1969, 14.50),
('Kindred', 2, 1979, 13.25);PRIMARY KEY identifies each row. The identity columns let PostgreSQL generate those IDs when rows are inserted. NOT NULL requires a value, CHECK enforces a condition, and REFERENCES connects a book to an existing author. Together these rules make invalid or incomplete data harder to store accidentally.
Read rows with SELECT. Choose only the columns you need, then filter with WHERE and order the result with ORDER BY:
SELECT title, published_year, price
FROM books
WHERE published_year >= 1970
ORDER BY published_year;The query returns the matching books, not a permanent change to the table. Add a row limit with LIMIT when you want only part of the result, for example LIMIT 10. PostgreSQL’s tutorial continues from table creation into inserting, querying, updating, deleting, joins, and aggregates.
How do I join related tables and summarize data?
A join combines rows from related tables. Because books.author_id refers to authors.author_id, you can display a book’s title alongside its author’s name:
SELECT authors.name, books.title, books.published_year
FROM books
JOIN authors ON authors.author_id = books.author_id
ORDER BY authors.name, books.published_year;An inner JOIN returns rows that match on the join condition. To include authors even if they have no books, start from authors and use a LEFT JOIN.
Aggregate functions summarize multiple rows. This query counts books and calculates their average price for each author:
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsSELECT authors.name,
count(books.book_id) AS book_count,
avg(books.price) AS average_price
FROM authors
LEFT JOIN books ON books.author_id = authors.author_id
GROUP BY authors.author_id, authors.name
ORDER BY authors.name;GROUP BY forms one group per author; count and avg calculate a result for each group. Use HAVING to filter groups after aggregation—for example, HAVING count(books.book_id) > 1.
How do updates, deletes, and transactions work?
UPDATE changes existing rows, and DELETE removes them. Always check the WHERE condition: without it, an update or delete applies to every row in the table.
UPDATE books
SET price = 15.00
WHERE title = 'Kindred';
DELETE FROM books
WHERE title = 'A Wizard of Earthsea';A transaction groups statements so they can be committed together or rolled back. This is useful when several changes are one logical operation:
BEGIN;
UPDATE books
SET price = price * 1.05
WHERE author_id = 2;
-- Check the result before making the change permanent.
SELECT title, price
FROM books
WHERE author_id = 2;
COMMIT;Use ROLLBACK; instead of COMMIT; to discard the transaction’s changes. Transactions help keep related changes consistent; they do not replace constraints such as foreign keys, which protect relationships regardless of how rows are edited.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
When should I use a foreign key, view, or window function?
Foreign keys for relationships
The books.author_id foreign key ensures that a book refers to an author that exists. If you try to insert a book with an unknown author ID, PostgreSQL rejects the statement. This is preferable to silently keeping a relationship that cannot be resolved. Foreign keys can also specify what should happen when a referenced row is updated or deleted; choose those rules deliberately based on the data’s meaning.
Views for reusable queries
A view gives a name to a query. It can make a frequently used join easier to read, though a regular view does not store a separate copy of the query’s results:
CREATE VIEW book_catalog AS
SELECT books.book_id,
books.title,
authors.name AS author,
books.published_year,
books.price
FROM books
JOIN authors ON authors.author_id = books.author_id;
SELECT title, author, price
FROM book_catalog
ORDER BY author, title;Window functions for comparisons within a result
A window function calculates across a related set of rows while leaving each row visible. For instance, rank each author’s books from most to least expensive without collapsing the rows into one result per author:
SELECT authors.name,
books.title,
books.price,
row_number() OVER (
PARTITION BY authors.author_id
ORDER BY books.price DESC
) AS price_rank
FROM books
JOIN authors ON authors.author_id = books.author_id;PARTITION BY starts the numbering again for each author. Unlike an aggregate query grouped by author, a window function keeps the individual books in the output.
Can PostgreSQL store and search JSON?
Yes. PostgreSQL supports JSON values and operators within SQL, alongside ordinary relational columns. Use a relational table for stable, shared properties that need relationships and constraints; JSON is useful for a value whose structure is naturally document-like or varies between records. JSON should not be a reason to give up useful relational modeling.
For many search and processing tasks, jsonb is useful because it supports operators and can be indexed with GIN. For example, add a JSONB column for optional descriptive attributes:
ALTER TABLE books
ADD COLUMN details jsonb NOT NULL DEFAULT '{}'::jsonb;
UPDATE books
SET details = '{"format": "paperback", "language": "English"}'::jsonb
WHERE title = 'Kindred';
SELECT title
FROM books
WHERE details ->> 'format' = 'paperback';JSONB offers different GIN operator classes. The default jsonb_ops class supports key-existence operators as well as containment and JSON path matches. jsonb_path_ops supports containment and JSON path matches, but not the key-existence operators. Choose based on the operators your queries need, not on a claim that one is universally faster. See PostgreSQL’s JSON types documentation for operators, JSON path support, and indexing details.
Which PostgreSQL index should I use?
An index can make a suitable query faster by helping PostgreSQL find rows without scanning the whole table, but it also takes space and adds work to data changes. An index is a match for a query pattern, not a general-purpose speed switch.
| Index type | Useful orientation |
|---|---|
| B-tree | Default index type; a common fit for equality and range searches on sortable data. |
| Hash | For equality comparisons. |
| GiST | An extensible index framework used by suitable operators and data types. |
| SP-GiST | An extensible framework for data structures that can be partitioned into non-overlapping regions. |
| GIN | Useful for values with multiple searchable components, including JSONB keys or key/value content. |
| BRIN | Can suit very large tables where values correlate with their physical row order. |
| bloom extension | An extension-provided index option, rather than one of the built-in index access methods. |
For example, if queries regularly filter books by publication year, a B-tree index is a reasonable candidate to evaluate:
CREATE INDEX books_published_year_idx
ON books (published_year);Do not add it just because the column exists. Consider the queries that matter and whether PostgreSQL actually uses the index; a small table or a query returning many rows may not benefit. The official indexes documentation describes index types, their costs, and how to assess them.
How do I back up a PostgreSQL database?
Backups are an operational requirement, not something a first SQL exercise establishes. PostgreSQL documents three broad approaches: SQL dumps, file-system-level backups, and continuous archiving. They have different assumptions and trade-offs, so the right method depends on how the database is deployed and what recovery the operator needs.
- SQL dump: exports database contents as SQL commands or an archive that can be restored. It is a logical backup approach.
- File-system-level backup: copies the database files using a method that respects PostgreSQL’s requirements for a consistent backup.
- Continuous archiving: combines a base backup with archived transaction log files to support recovery to a point in time.
A backup plan also needs decisions about retention, restore testing, recovery objectives, and deployment-specific procedures. A file copied once is not proof that a usable recovery is available. Read the full backup and restore documentation before choosing a production method.
What should I learn after this quick start?
Use the PostgreSQL 18 tutorial to continue practicing the introductory SQL path. For language details beyond the examples here, move to the SQL section of the official documentation index. If you are writing an application, consult its application-development documentation for client behavior and connection handling. If you operate the server, continue into the administration and backup chapters rather than treating a few SQL commands as preparation for production.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
