What is PostgreSQL?

PostgreSQL is the world's most advanced open-source relational database. It's free, incredibly powerful, and trusted by developers everywhere. For three consecutive years, PostgreSQL has ranked as the most admired database on Stack Overflow โ€” used by companies like Stripe, Instagram, and Spotify.

In this lesson, you'll install PostgreSQL locally and run your first queries. By the end, you'll have a working database and understand the basics of psql (PostgreSQL's interactive terminal).

Install PostgreSQL

PostgreSQL is available for Windows, macOS, and Linux. Choose your operating system below:

Ubuntu/Debian Installation

Open your terminal and run the following commands:

bash

This installs PostgreSQL and enables it to start automatically on system boot.

macOS Installation

Using Homebrew (if you don't have Homebrew, install it first from brew.sh):

bash

Homebrew handles the installation and automatically starts the PostgreSQL service.

Windows Installation

Download the installer from postgresql.org/download/windows, then run the .exe file. The wizard will guide you through:

  • Installation directory (default: C:\Program Files\PostgreSQL)
  • A password for the postgres user (save this!)
  • Port selection (default: 5432)

After installation, PostgreSQL runs as a Windows service and starts automatically.

Your First Connection โ€” psql

psql is PostgreSQL's interactive terminal. It's where you'll write SQL queries, manage databases, and explore your data. Let's connect to PostgreSQL:

bash
psql (17.0)
Type "help" for help.

postgres=#

You're now in the psql prompt. The postgres=# indicator tells you that you're connected to the default postgres database as the postgres user.

Essential psql Commands

Here are the most important psql commands you'll use:

\l โ€” List all databases
\c dbname โ€” Connect to a specific database
\dt โ€” List all tables in the current database
\d tablename โ€” Describe a table (show its columns)
\q โ€” Quit psql and return to the shell
\? โ€” Show all psql commands

These backslash commands are psql-specific. Regular SQL commands (SELECT, CREATE, INSERT, etc.) don't use a backslash.

Create Your First Database

Let's create a database called quickcart โ€” the fictional Indian quick-commerce company we'll use throughout this track:

sql
CREATE DATABASE
You are now connected to database "quickcart" as user "postgres".

The first command creates the database. The second command (\c quickcart) connects you to it. Notice the prompt changed to quickcart=#.

Test It โ€” Create, Insert, Query, Drop

Let's walk through a complete workflow: create a table, insert data, query it, and clean up.

Q1: Create a Table

sql
CREATE TABLE

SERIAL auto-increments the id. DEFAULT NOW() automatically records the insertion time.

Q2: Check Your PostgreSQL Version

sql
version
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.3.0, 64-bit

Q3: Insert a Row

sql
INSERT 0 1

The output INSERT 0 1 means 1 row was inserted.

Q4: Query the Table

sql
id | message | created_at
โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
1 | PostgreSQL is working! | 2026-04-16 14:19:33.500089

You now have data in your database! The created_at timestamp was automatically set.

Q5: Drop the Table

sql
DROP TABLE

We clean up after ourselves. This removes the test table since we won't use it anymore.

Download Setup Check Script

You can download a SQL script that runs all the above queries at once. This is helpful for verifying your PostgreSQL installation:

๐Ÿ“ฅ Download setup-check.sql

To run it: psql quickcart -f setup-check.sql

Troubleshooting Common Errors

Error: "psql: could not connect to server"

Cause: PostgreSQL service isn't running.

Fix: Start the service:

bash
Error: "role does not exist"

Cause: You're trying to connect as a user that doesn't exist.

Fix: Create the user:

bash
Error: "permission denied"

Cause: PostgreSQL client (psql) configuration issue.

Fix: Check your pg_hba.conf file (usually in /etc/postgresql/XX/main/ on Linux) and ensure your user is authorized.

Challenge

Create a database called shopstream and verify it appears in your database list.

Hint: Use CREATE DATABASE to create it, then use \l to list all databases.

Show hint

Run these two commands:

sql

Test Your Knowledge

Question 1 of 5
What command lists all databases in psql?
Question 2 of 5
What does SERIAL do in a column definition?
Question 3 of 5
Which command connects to a specific database?
Question 4 of 5
What is psql?
Question 5 of 5
What does DEFAULT NOW() do?
0
/5
Continue to Lesson 03 โ†’
Africa & India's Best AI Academy
๐Ÿค– AI-first ๐ŸŽ“ Placement Assistance

PostgreSQL is running. Now build something real.

Join the Best AI Program: The AI-Era Data Engineer โ€” 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Data Engineer โ†’