Learning SQL by reading is like learning to swim by watching videos: necessary, but not enough. For this course to be genuinely useful you need an environment where you can write queries, get them wrong, read the error message and try again. This lesson walks you from "I have nothing installed" to "I have PostgreSQL 16 running, I know how to connect and I've created the greenstore database". We'll cover installation on Linux, macOS and Windows, the Docker alternative (the one we recommend), an emergency exit with SQLite if you can't install anything on your machine, the essential commands of the psql client and the most widely used graphical clients.

1. What exactly you need
2. Option A (recommended): PostgreSQL with Docker
3. Option B: native installation on Linux, macOS and Windows
4. Option C (minimal): SQLite
5. Connecting with psql and its basic commands
6. Graphical clients: pgAdmin, DBeaver and VS Code
7. Creating the greenstore database and a user of your own
8. Checking that everything works
9. Common Mistakes and Tips
10. Exercises
11. Conclusion

To follow the course you need three pieces:

And you need to decide how you're going to install the server. These are the three routes, ordered by what we recommend:

Docker lets you spin up PostgreSQL 16 in an isolated container, with exactly the version the course uses, without touching the rest of your system.

It's worth understanding each option, because you'll see them again:

In the logs you should see a line similar to database system is ready to accept connections.

Note: if port 5432 is already taken by another PostgreSQL installation, change the publication to -p 5433:5432 and use -p 5433 when connecting.

Details that tend to cause trouble:

• Linux/Debian: after installing, local access goes through peer authentication. The right way in the first time is sudo -u postgres psql, not plain psql.
• macOS with Homebrew: if psql isn't found, add the path to your PATH:

  echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
  source ~/.zshrc
  

• Windows: the installer doesn't always add psql to the PATH. If PowerShell tells you the command isn't recognised, add C:\Program Files\PostgreSQL\16\bin to the Path environment variable by hand, or use the "SQL Shell (psql)" shortcut in the Start menu.

If you work on a machine where you can't install services, SQLite will let you follow a good part of the course. A SQLite database is a single file and there's no server to start.

Creating a database and opening it are the same thing:

Its client commands start with a dot instead of a backslash:

Honest limitations of SQLite for this course: it has no users or permissions (module 11), its types are dynamic and very permissive (modules 1 and 5), it doesn't support RIGHT/FULL OUTER JOIN in versions before 3.39, its EXPLAIN is different (module 8) and it has no equivalent stored procedures or triggers (module 10). Use it as a last resort; if at any point you can switch to PostgreSQL, do it.

psql is the official command-line client. It's the tool we'll use in the explanations because it's fast, it's always available and it shows errors exactly as the server produces them.

The options repeat across almost every PostgreSQL tool:

Once you're inside, the prompt tells you the state you're in:

That third prompt (-#) is the one that puzzles people most at the start: it means psql is waiting for you to close the statement with ;.

Commands that start with \ are not SQL: the client itself interprets them, which is why they take no semicolon.

Example of a typical session:

A graphical client doesn't replace psql, but it speeds up exploring an unfamiliar schema enormously.

Connection details any of them will ask you for (with the Docker setup from section 2):

Let's prepare the course workspace. Connect first as superuser:

And run:

What each part does:

• CREATE USER creates a role that can log in. Working as superuser every day is a bad habit: an accidental DROP would meet no barrier at all.
• OWNER sql_course makes that user the owner of the database so it can create tables inside without extra permissions.
• ENCODING 'UTF8' guarantees that the accented characters in our data (Castellón, Lucía Martínez Soler) are stored correctly.
• LC_COLLATE defines the language's alphabetical order: with the English setting, ORDER BY will place "apple" before "Banana", as you'd expect, instead of grouping all the capitals first.

If LC_COLLATE gives you an error: in the PostgreSQL Docker image that locale may not have been generated. In that case, create the database without those two clauses:

CREATE DATABASE greenstore OWNER sql_course ENCODING 'UTF8';

It affects nothing in the course beyond small nuances of alphabetical ordering.

Now connect to the new database with your user:

And grant permissions on the public schema (necessary since PostgreSQL 15, which tightened the default permissions). This runs as superuser:

The canonical check is to ask the server for its version:

Expected result (the exact text will vary depending on your system):

A couple more checks, useful for confirming you're where you think you are:

And a quick test that you can create objects in the database:

If the four statements run without error, your environment is ready. With DROP TABLE test_table; you leave the database clean for the real script, which you'll load in lesson 01-06.

• psql: error: connection to server ... failed: Connection refused. The server isn't running or the port is a different one. With Docker, check docker ps; with a native installation, sudo systemctl status postgresql.
• FATAL: password authentication failed for user "postgres". Wrong password, or you're using the one from another environment. In Docker, the password is the one you passed in POSTGRES_PASSWORD the first time the volume was created: changing it in a later docker run has no effect if the volume already exists.
• FATAL: role "your_user" does not exist. On Linux, psql tries to connect with your system user. Use -U postgres explicitly or get in with sudo -u postgres psql.
• FATAL: database "greenstore" does not exist. You haven't created it yet, or you created it on a different server from the one you're querying.
• Forgetting the semicolon. If the prompt goes from =# to -#, psql is waiting for the ;. Type it and press Enter.
• Losing your data when recreating the container. Without -v there's no volume, and deleting the container deletes the whole database. It's the most painful Docker mistake.
• Tip: save your queries in .sql files. Create a sql-course/ folder with one file per module. You'll be able to run them with \i and you'll keep a record of your progress.
• Tip: turn \timing on from day one. You'll get used to looking at how long each query takes, something that will feel natural by the time you reach module 8.
• Tip: use \x with wide tables. When a row doesn't fit on screen and the result is unreadable, \x turns it into a vertical list of fields.

Start PostgreSQL 16 with Docker on port 5433 instead of 5432 (useful if you already have another PostgreSQL on your machine), with the container name pg-greenstore and a volume called gs-data. Then connect with psql and check the server version.

From psql, and without leaving the client, answer these three questions using metacommands (not SQL):

1. Which databases exist on your server?
2. Which users/roles are defined?
3. What is the full syntax of the CREATE DATABASE statement?

Create a database called sql_tests owned by the sql_course user, connect to it, create a notes(id, content) table, insert two rows, query them and finally drop the whole database.

Solution 1

The key detail is -p 5433:5432: the number on the left is the port on your machine and the one on the right the container's (which is always 5432, because inside the container PostgreSQL listens there). To connect:

Solution 2

\l (for list) shows databases with their owner and encoding; \du (display users) shows roles and their attributes; \h gives SQL syntax help, whereas \? gives the metacommand help. Remember: none of them takes a semicolon, because they aren't SQL.

Solution 3

To drop the database you can't be connected to it, so first go back to postgres:

If you try the DROP while inside, PostgreSQL answers ERROR: cannot drop the currently open database. It's a very frequent error and the fix is always the same: switch database first.

You now have a real working environment:

• You know the three routes to having PostgreSQL 16: Docker (reproducible and recommended), native installation per operating system and SQLite as a last resort.
• You know the psql client, its connection options (-h, -p, -U, -d, -f, -c) and its key metacommands (\l, \c, \dt, \d, \x, \timing, \?, \q).
• You can choose a graphical client on solid grounds: DBeaver as the general option, pgAdmin for administration, VS Code if you already work there.
• You've created the greenstore database and the sql_course user, and you've verified the installation with SELECT version();.

In the next lesson, Basic SQL Syntax, we'll stop installing and start writing: you'll see how a SQL statement is built on the inside (keywords, identifiers, literals, operators), what role the semicolon plays, how PostgreSQL treats upper and lower case, how to comment your code, what formatting conventions professionals use and —very importantly— how to read an error message so you know exactly where you went wrong.