My Beginner Journey with PostgreSQL: Learning One Command at a Time

illustration

🌱 My Beginner Journey with PostgreSQL: Learning One Command at a Time

When I decided to learn PostgreSQL, I didn’t know where to start. I had a vague idea that it was a database, that it used SQL, and that developers seemed to love it. But I was completely new to databases. I wasn’t working in a team, I had no mentor, and this wasn’t for any job or class—I was just curious and wanted to grow my skills.

This is my personal journey learning PostgreSQL from scratch. I’ll walk you through what I’ve done so far: installing PostgreSQL, creating databases, writing my first SQL queries, and understanding concepts like tables, indexes, joins, and more. If you're also starting out, I hope this helps you feel less overwhelmed.

---

🌟 Why I Chose PostgreSQL as a Beginner

At first, I considered MySQL and SQLite because they seemed easier. But when I looked deeper, I saw PostgreSQL recommended again and again for being reliable, powerful, and open source. What really sold me was that it’s used by professionals but still accessible to learners. It felt like a tool I could grow with.

So I made a decision: I’d start learning PostgreSQL by setting it up locally and figuring things out one step at a time.

---

🛠️ Installing PostgreSQL on My Computer

Since I use Ubuntu, I installed PostgreSQL using the terminal:

bash

sudo apt update
sudo apt install postgresql postgresql-contrib

After the installation finished, I switched to the PostgreSQL user:

bash

sudo -i -u postgres
psql

I was suddenly inside something called the PostgreSQL shell. It looked like a new world. I typed:

sql

\l

And saw a list of databases already created. That was my first interaction with a database system. I had no idea what I was doing, but I felt proud that I had installed it successfully.

---

🧠 Creating My First User and Database

I didn’t want to keep using the default postgres user. I wanted to create my own user and my own database—just to understand how access and ownership work.

Here’s what I did:

sql

CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydb OWNER myuser;

Then I exited psql and connected again as my new user:

bash

psql -U myuser -d mydb

Now I was inside my own database, using my own user. It felt real. Like I had built something, even though it was just a blank database.

---

📦 Creating My First Table

I decided to keep things simple and create a table of users:

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT,
    email TEXT
);

I didn’t fully understand what SERIAL or PRIMARY KEY meant, but I read that it automatically creates a unique ID for each row, which sounded useful.

Then I inserted my first record:

sql

INSERT INTO users (name, age, email)
VALUES ('Alice', 25, 'alice@example.com');

And then ran my first query:

sql

SELECT * FROM users;

I saw the data I had just entered. It was an amazing feeling.

---

🔍 Learning SQL Through Practice

To learn SQL, I started doing small experiments. I added more users, then filtered them using WHERE:

sql

SELECT * FROM users WHERE age > 22;

I also tried updating and deleting data:

sql

UPDATE users SET age = 26 WHERE name = 'Alice';
DELETE FROM users WHERE name = 'Bob';

Every time I ran a command and it worked, I felt a little more confident. I didn’t rush. I just kept testing, failing, and learning.

---

🔁 Understanding Relationships: My First Join

After I got comfortable with one table, I wanted to try working with two. So I created a departments table:

sql

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

And added a reference to it in my users table:

sql

ALTER TABLE users ADD COLUMN department_id INT;

Then I inserted some departments and linked them to users.

To join them, I ran:

sql

SELECT users.name, departments.name AS department
FROM users
JOIN departments ON users.department_id = departments.id;

This was the first time I saw a real connection between two tables. It helped me understand how databases organize information.

---

🧰 Trying Tools to Make Life Easier

Working in the terminal is fine, but I wanted to see my tables in a nice interface. I installed pgAdmin 4, and it helped a lot. I could view tables, run SQL queries, and manage my database with clicks instead of commands.

Later, I also tried DBeaver, which I liked for its clean design. These tools made PostgreSQL feel more approachable.

---

🔎 My First Time Using Indexes

At one point, I had over 5000 fake users in my table (I used a Python script to generate them). I noticed that some queries were taking longer.

So I read about indexes and decided to try one:

sql

CREATE INDEX idx_email ON users(email);

When I searched by email:

sql

SELECT * FROM users WHERE email = 'alice@example.com';

It was faster than before. I also learned how to check query performance using:

sql

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

It felt technical, but exciting. I was optimizing my own database!

---

🔄 Backup and Restore: Learning to Protect My Data

I wanted to learn how to back up my work, in case I messed something up. PostgreSQL made it easy.

To back up:

bash

pg_dump -U myuser mydb > mydb_backup.sql

To restore:

bash

psql -U myuser -d mydb < mydb_backup.sql

I practiced this a few times and even deliberately deleted data to test the restore. It worked every time.

---

🔡 JSON Columns: Something I Didn’t Expect to Learn

I found out that PostgreSQL supports JSON columns. That was new to me. I created a table for products with flexible metadata:

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    details JSONB
);

Then added a product:

sql

INSERT INTO products (name, details)
VALUES ('Laptop', '{"brand": "HP", "RAM": "16GB"}');

I could even query inside the JSON:

sql

SELECT * FROM products WHERE details->>'brand' = 'HP';

It blew my mind that a relational database could handle this kind of data.

---

🚀 Trying PostgreSQL with Python

Since I knew a bit of Python, I wanted to connect it to my PostgreSQL database. I installed the psycopg2 library:

bash

pip install psycopg2

Then wrote a simple script:

python

import psycopg2

conn = psycopg2.connect(
    dbname="mydb",
    user="myuser",
    password="mypassword",
    host="localhost"
)

cur = conn.cursor()
cur.execute("SELECT * FROM users;")
rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
conn.close()

It was awesome to see my database working with my code. It made everything feel more “real-world.”

---

🔐 Understanding Roles (Even Just for Myself)

Even though I’m working solo, I practiced creating multiple roles—just to understand how PostgreSQL manages permissions. I made a read-only user:

sql

CREATE ROLE readonly WITH LOGIN PASSWORD 'readonlypass';
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Then I logged in as readonly and confirmed that I couldn’t insert or update anything. This gave me a small taste of what it would be like to manage users in a real app.

---

💭 What I Struggled With (and How I Overcame It)

Learning PostgreSQL hasn’t been 100% smooth. Some things confused me:

• Understanding joins took me a while. I drew diagrams to help.
• I didn’t understand schemas at first—I thought they were folders!
• I kept forgetting to end SQL commands with a semicolon.
• I often messed up quoting strings and identifiers.

But I kept practicing. I watched YouTube tutorials, read the official documentation, and asked questions on forums like Stack Overflow. Each time I hit a wall, I tried again the next day.

---

📈 My Future PostgreSQL Goals

Now that I’ve grasped the basics, here’s what I plan to explore next:

• Learning about views and materialized views
• Writing stored procedures with PL/pgSQL
• Understanding triggers
• Trying PostGIS for maps and spatial data
• Hosting a PostgreSQL database on the cloud (like Supabase)

This learning journey feels endless, but in a good way. PostgreSQL gives me room to grow.

---

✅ Final Thoughts: Why I’m Sticking with PostgreSQL

Even as a beginner, I feel like PostgreSQL is a great tool to learn. It’s professional, but welcoming. Every new feature I learn opens up new possibilities.

Here’s why I like it:

• It’s free and open-source
• It supports both structured and flexible data
• It works great with tools and languages I already know
• It teaches me real-world database concepts I can use anywhere

---

🏁 What I’d Tell Other Beginners

If you’re thinking of learning PostgreSQL, here’s my advice:

Just start. Don’t wait until you “know enough.” Create a database, run a query, and see what happens.

You don’t need a job, a team, or a project. Learning for yourself is more than enough. That’s what I’m doing, and it’s one of the most rewarding things I’ve learned so far.

You might also like this article