PostgreSQL for beginners: installing and first steps

Why this database

It appears in entry-level postings more than any other. It's free, runs everywhere, does everything you'll need for years, and what you learn on it transfers almost entirely to other databases.

Installing

Linux. One command through your system's package manager.

macOS. Through a package manager, or a ready application with a graphical launcher — the latter is easier to begin with.

Windows. The installer from the official site. During installation, remember the password you set for the main user: you'll be asked for it.

The simplest option for learning is running the database in a container. One command, nothing left behind in the system, easy to delete and start over.

Connecting

The built-in client runs from a terminal:

psql -U postgres

The flag names the user. You'll then be asked for the password.

Useful commands inside:

\l          list databases
\c name     switch to a database
\dt         list tables
\d table    describe a table
\q          quit

At first a graphical tool is more comfortable; several free ones exist. But learn the commands above: on a server there'll be no graphics.

Creating a database and a table

CREATE DATABASE notes;

Switch to it:

\c notes

Create a table:

CREATE TABLE note (
  id serial PRIMARY KEY,
  title text NOT NULL,
  body text,
  created_at timestamp DEFAULT now()
);

Breaking that down: the first field is an automatic number serving as the key. The title is required. The body may be empty. The date fills itself in.

Add a row:

INSERT INTO note (title, body) VALUES ('First', 'Testing');

Look at it:

SELECT * FROM note;

Connecting from a program

Code connects with a string containing: database type, user, password, host, port, database name.

The main rule: the connection string must not live in the code. Move it to environment variables. A password that reached a repository should be considered exposed.

When it won't connect

Check the service is running. The most common cause: the database simply isn't up.

Check the port. The default is standard; if another program took it, the database may be listening elsewhere.

Check connection permissions. This database has its own access rules file, and by default it often permits only local connections by a particular method. The error message usually names that file directly.

Check the password. Especially if you copied a connection string: characters like the at sign or a colon in a password must be encoded.

What to learn next

Backups and restores — worth learning immediately, while the data is practice data and losing it costs nothing. Later, the habit will save a real project.