Setting Up PostgreSQL

Installing PostgreSQL

Before writing any SQL you need a running database. PostgreSQL is available on all major operating systems, and installation takes only a few minutes. The goal here is to get a local instance running so you can follow along with every example in this series.

macOS (Homebrew)

Homebrew is the fastest way to get PostgreSQL running on a Mac. It handles versioning cleanly and makes starting and stopping the server a single command.

brew install postgresql@17
brew services start postgresql@17

After installation, add the binaries to your PATH (Homebrew will print the exact command, usually something like):

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

Verify the installation:

psql --version
# psql (PostgreSQL) 17.x

Windows

Download the interactive installer from postgresql.org/download/windows. The installer includes PostgreSQL, pgAdmin (a GUI tool), and the Stack Builder for optional extensions.

During installation you’ll set a password for the postgres superuser — keep it somewhere safe. After installation, add the PostgreSQL bin directory to your system PATH (usually C:\Program Files\PostgreSQL\17\bin) so you can run psql from any terminal.

Linux (Debian / Ubuntu)

On Debian-based systems the package manager handles everything, including creating the system user and initializing the data directory.

sudo apt update
sudo apt install postgresql postgresql-contrib

# Start the service and enable it on boot
sudo systemctl start postgresql
sudo systemctl enable postgresql

On RHEL/CentOS/Fedora, use dnf instead of apt:

sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb
sudo systemctl start postgresql

Connecting with psql

psql is the official command-line client. It’s worth learning because it works everywhere, requires no additional setup, and exposes features that graphical tools sometimes hide. Once PostgreSQL is running, connect as the default superuser:

# macOS / Linux
psql -U postgres

# If your system created a postgres OS user (common on Linux):
sudo -u postgres psql

You’ll see a prompt like postgres=#. You’re now connected to the default postgres database.

Essential psql Meta-Commands

psql has a set of backslash commands that help you navigate the database without writing SQL. These are psql-specific — they don’t work in other clients and won’t appear in query logs.

Example session:

postgres=# \l
                 List of databases
   Name    | Owner  | ...
-----------+--------+
 postgres  | postgres
 template0 | postgres
 template1 | postgres

postgres=# \c postgres
You are now connected to database "postgres".

postgres=# \dt
Did not find any relations.

Creating Your First Database

Working in a dedicated database keeps your experiments separate from the default postgres database and gives you a clean slate you can always drop and recreate. This is also the pattern you’ll follow in real projects.

CREATE DATABASE learn_sql;

Connect to it:

\c learn_sql

Now create a simple table and insert some data:

-- A minimal 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 three rows at once
INSERT INTO products (name, price) VALUES
    ('Keyboard', 89.99),
    ('Mouse',    34.50),
    ('Monitor', 349.00);

-- Retrieve all rows
SELECT * FROM products;

Expected output:

 id |   name   |  price
----+----------+--------
  1 | Keyboard |  89.99
  2 | Mouse    |  34.50
  3 | Monitor  | 349.00
(3 rows)

Use \d products to inspect the table structure:

                         Table "public.products"
 Column |     Type      | Nullable |           Default
--------+---------------+----------+------------------------------
 id     | integer       | not null | nextval('products_id_seq')
 name   | text          | not null |
 price  | numeric(10,2) |          |

GUI Tools

If you prefer a graphical interface, several good options exist. Each one connects using the same credentials — host localhost, port 5432, user postgres, and the password you set during installation — so switching between them is easy.

• pgAdmin — the official PostgreSQL GUI, included with the Windows installer. Feature-rich but can feel heavy for everyday use.
• DBeaver — free, open-source, supports PostgreSQL and dozens of other databases. Good all-around choice.
• TablePlus — polished, fast, native app for macOS and Windows. Free tier is usable; paid tier removes tab limits.
• DataGrip — JetBrains’ database IDE. Excellent autocomplete and refactoring tools. Paid, but included in JetBrains All Products Pack.

Connection String Format

PostgreSQL accepts connections via a URI string. This format is how applications connect — Node.js, Python, Go, and most ORMs accept it directly, so understanding it early saves confusion when you move from psql to application code.

postgresql://username:password@hostname:port/database

For local development:

postgresql://postgres:yourpassword@localhost:5432/learn_sql

Most application libraries (Node’s pg, Python’s psycopg2, SQLAlchemy, etc.) accept this format directly.

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.