Setting Up PostgreSQL

You need a running PostgreSQL server and the psql client before any SQL in this series will run. Docker is the quickest route on every operating system, and it is the one the later lessons assume, so it comes first. Native installs follow if you’d rather not use Docker.

Running PostgreSQL in Docker

Start a PostgreSQL 18 container, then wait until it accepts connections before you use it.

docker run -d --name pr-sqlsetup-pg -p 5432:5432 -e POSTGRES_PASSWORD=secret postgres:18
until docker exec pr-sqlsetup-pg pg_isready -h localhost -q; do sleep 1; done
docker exec pr-sqlsetup-pg pg_isready -h localhost
docker exec pr-sqlsetup-pg psql --version

$ docker run -d --name pr-sqlsetup-pg -p 5432:5432 -e POSTGRES_PASSWORD=secret postgres:18
f8804f3611db62219fe11210b9315c9b7950e17c663424833c43f957dd09ee7d
$ until docker exec pr-sqlsetup-pg pg_isready -h localhost -q; do sleep 1; done
$ docker exec pr-sqlsetup-pg pg_isready -h localhost
localhost:5432 - accepting connections
$ docker exec pr-sqlsetup-pg psql --version
psql (PostgreSQL) 18.6 (Debian 18.6-1.pgdg13+2)

docker run -d returns as soon as the container exists, long before the server is ready, so the until loop polls pg_isready once a second. The -h localhost matters. On first start the image runs a temporary server to create the database, and that server listens only on a Unix socket. A plain pg_isready can report “accepting connections” from it just before it shuts down, while -h localhost waits for the real server’s TCP port. -p 5432:5432 publishes that port to your machine, so GUI tools and app code can reach it at localhost:5432. If you already have PostgreSQL on 5432, use -p 5433:5432 and port 5433 instead.

If a later lesson reads files in the server’s data directory, it says which image tag to use.

Installing PostgreSQL natively

If you’d rather run the server directly on your machine, install it from your package manager. Each of these gives you the server and psql, and you then run psql without the docker exec pr-sqlsetup-pg prefix. On Debian and Ubuntu, local logins use peer authentication, so run it as the postgres user: sudo -u postgres psql -d learn_sql < products.sql.

# macOS (Homebrew)
brew install postgresql@18
brew services start postgresql@18

# Debian / Ubuntu (the distribution's current major version)
sudo apt install postgresql
sudo systemctl enable --now postgresql

On Windows, use the installer from postgresql.org/download/windows. It sets a password for the postgres user, includes pgAdmin, and installs psql in C:\Program Files\PostgreSQL\18\bin, which you add to your PATH. On Linux, connect with sudo -u postgres psql, because the package authenticates the postgres database user through the postgres operating-system user. Homebrew makes your own login the superuser, so there you run plain psql postgres.

Connecting with psql

psql is the official command-line client. Open an interactive session inside the container, list the databases, create one for this series, and switch to it.

\l
CREATE DATABASE learn_sql;
\c learn_sql
\dt
\q

$ docker exec -it pr-sqlsetup-pg psql -U postgres
psql (18.6 (Debian 18.6-1.pgdg13+2))
Type "help" for help.

postgres=# \l
                                                    List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+------------+------------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | 
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/postgres          +
           |          |          |                 |            |            |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/postgres          +
           |          |          |                 |            |            |        |           | postgres=CTc/postgres
(3 rows)

postgres=# CREATE DATABASE learn_sql;
CREATE DATABASE
postgres=# \c learn_sql
You are now connected to database "learn_sql" as user "postgres".
learn_sql=# \dt
Did not find any tables.
learn_sql=# \q

-it gives psql a terminal, so you get the postgres=# prompt. The prompt shows the current database, which is why it changes to learn_sql=# after \c. A fresh server has three databases. postgres is the default one you land in, and template0/template1 are the templates that CREATE DATABASE copies, which is why their access-privilege column is filled in. Commands that start with a backslash are handled by psql itself, not sent to the server as SQL, so they need no semicolon. CREATE DATABASE is SQL and does need one.

These are the backslash commands you’ll use most:

Loading a seed file

Longer SQL belongs in a file. Save this as products.sql. Then feed it to psql on standard input. Use -i without -t, because the input is a file and not your keyboard.

-- products.sql: a table with an auto-incrementing id, a name and a price
CREATE TABLE products (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    price NUMERIC(10, 2)
);

INSERT INTO products (name, price) VALUES
    ('Keyboard', 89.99),
    ('Mouse',    34.50),
    ('Monitor', 349.00);

SELECT * FROM products;

$ docker exec -i pr-sqlsetup-pg psql -U postgres -d learn_sql < products.sql
CREATE TABLE
INSERT 0 3
 id |   name   | price  
----+----------+--------
  1 | Keyboard |  89.99
  2 | Mouse    |  34.50
  3 | Monitor  | 349.00
(3 rows)

psql echoes a status tag for each statement. INSERT 0 3 means three rows were inserted (the 0 is a leftover object-id field that is always 0 now). The ids 1, 2, 3 came from SERIAL, so you never supplied them. Column headers are centred, while numbers are right-aligned and text is left-aligned. That’s why price sits in the middle of its column, while 89.99 lines up with the right edge.

Ask psql to describe the table you just made:

$ docker exec pr-sqlsetup-pg psql -U postgres -d learn_sql -c '\d products'
                               Table "public.products"
 Column |     Type      | Collation | Nullable |               Default                
--------+---------------+-----------+----------+--------------------------------------
 id     | integer       |           | not null | nextval('products_id_seq'::regclass)
 name   | text          |           | not null | 
 price  | numeric(10,2) |           |          | 
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)

SERIAL isn’t a real type. It is shorthand for an integer column whose default calls nextval on a sequence that PostgreSQL created for you (products_id_seq). PRIMARY KEY added the not null and the products_pkey index. The table lives in the public schema, which is where tables go when you don’t name a schema. -c runs one command and exits, which is handy for scripts.

GUI tools

Every GUI client connects with the same five values: host localhost, port 5432, user postgres, the password (secret for the container above), and a database such as learn_sql.

• pgAdmin is the official PostgreSQL GUI, and the Windows installer includes it.
• DBeaver is free and open source, and it supports dozens of other databases too.
• DataGrip is JetBrains’ paid database IDE, with strong autocomplete.

Connection strings

Applications usually take those five values as a single URI. Node’s pg, Python’s psycopg, SQLAlchemy and most ORMs accept it directly.

postgresql://username:password@hostname:port/database
postgresql://postgres:secret@localhost:5432/learn_sql

The second line is the container above. Practice task 4 below connects with one.

Practice

The solutions were run on PostgreSQL 18.6 in the pr-sqlsetup-pg container from this lesson. Each one is saved as a .sql file and fed to psql on standard input, so you can see what every statement prints. Start each task from the products table as the previous one left it.

1. Starting from the three products above, insert two more rows in one statement: a Webcam priced 59.999 and a Cable with no price (NULL). Before you run SELECT *, predict what the Webcam’s price column will show.

-- setup1.sql: two more products, one rounded and one without a price
INSERT INTO products (name, price) VALUES
    ('Webcam', 59.999),
    ('Cable',  NULL);

SELECT * FROM products;

$ docker exec -i pr-sqlsetup-pg psql -U postgres -d learn_sql < setup1.sql
INSERT 0 2
 id |   name   | price  
----+----------+--------
  1 | Keyboard |  89.99
  2 | Mouse    |  34.50
  3 | Monitor  | 349.00
  4 | Webcam   |  60.00
  5 | Cable    |       
(5 rows)

2. Try to insert a product with only a price: INSERT INTO products (price) VALUES (19.99);. Then insert ('Headset', 79.00) normally and select everything. What id does the Headset get?

INSERT INTO products (price) VALUES (19.99);

INSERT INTO products (name, price) VALUES ('Headset', 79.00);

SELECT * FROM products;

$ docker exec -i pr-sqlsetup-pg psql -U postgres -d learn_sql < setup2.sql
ERROR:  null value in column "name" of relation "products" violates not-null constraint
DETAIL:  Failing row contains (6, null, 19.99).
INSERT 0 1
 id |   name   | price  
----+----------+--------
  1 | Keyboard |  89.99
  2 | Mouse    |  34.50
  3 | Monitor  | 349.00
  4 | Webcam   |  60.00
  5 | Cable    |       
  7 | Headset  |  79.00
(6 rows)

3. Use two meta-commands from the table: turn on \timing, run SELECT * FROM products;, list the tables with \dt, then turn timing off again.

\timing
SELECT * FROM products;
\dt
\timing

$ docker exec -i pr-sqlsetup-pg psql -U postgres -d learn_sql < setup3.sql
Timing is on.
 id |   name   | price  
----+----------+--------
  1 | Keyboard |  89.99
  2 | Mouse    |  34.50
  3 | Monitor  | 349.00
  4 | Webcam   |  60.00
  5 | Cable    |       
  7 | Headset  |  79.00
(6 rows)

Time: 0.650 ms
            List of tables
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | products | table | postgres
(1 row)

Timing is off.

4. Connect with a connection string instead of -U and -d, and run one query in the same command with -c. Then repeat it with a wrong password in the URI. If you don’t have psql installed locally, run it from a throwaway postgres:18 container, as here. It reaches the published port through host.docker.internal.

$ docker run --rm --add-host=host.docker.internal:host-gateway postgres:18 psql "postgresql://postgres:secret@host.docker.internal:5432/learn_sql" -c "SELECT * FROM products;"
 id |   name   | price  
----+----------+--------
  1 | Keyboard |  89.99
  2 | Mouse    |  34.50
  3 | Monitor  | 349.00
  4 | Webcam   |  60.00
  5 | Cable    |       
  7 | Headset  |  79.00
(6 rows)

$ docker run --rm --add-host=host.docker.internal:host-gateway postgres:18 psql "postgresql://postgres:wrong@host.docker.internal:5432/learn_sql" -c "SELECT * FROM products;"
psql: error: connection to server at "host.docker.internal" (192.168.65.254), port 5432 failed: FATAL:  password authentication failed for user "postgres"

Next Steps

With PostgreSQL running and a database created, you’re ready to start writing real SQL. The next tutorial covers data types — understanding what kinds of values each column can hold is the foundation for good schema design.