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:
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):
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
postgresuser (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:
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:
\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:
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
SERIAL auto-increments the id. DEFAULT NOW() automatically records the insertion time.
Q2: Check Your PostgreSQL Version
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.3.0, 64-bit
Q3: Insert a Row
The output INSERT 0 1 means 1 row was inserted.
Q4: Query the Table
โโโโโผโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
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
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.sqlTo 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:
Error: "role does not exist"
Cause: You're trying to connect as a user that doesn't exist.
Fix: Create the user:
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.
Run these two commands:
Test Your Knowledge
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 โ