InnoMarts logo
InnoMarts
Fast websites
← Back to course

Getting Started with PostgreSQL

Getting Started with PostgreSQL

Welcome to the PostgreSQL course.

Yes.

We are entering the database world.

Do not panic.

A database is not a dragon living under your server.

Although sometimes it behaves like one.

PostgreSQL is one of the most powerful and respected database systems in the world.

It is used by developers, companies, startups, banks, web apps, APIs, dashboards, and probably at least one mysterious system nobody dares to touch because “it works, do not restart it.”

In this course, we will learn PostgreSQL step by step.

No magic.

No scary theory dump.

No “just read 900 pages of documentation and become enlightened.”

We will build understanding through practice.

Today we start with the basics.

What PostgreSQL is.

How it works.

How to install it.

How to open the PostgreSQL shell.

How to create a database.

How to create a table.

How to insert data.

How to read data.

Small steps.

Real progress.

Very database.

What You Will Learn

In this lesson, you will learn:

By the end of this lesson, you will have a working PostgreSQL database on your computer.

Not just theory.

A real database.

Small.

Fresh.

Ready to store data.

Like a notebook.

But with more semicolons.

What Is PostgreSQL?

PostgreSQL is a relational database management system.

That sounds serious.

Because it is.

But the idea is simple.

PostgreSQL stores data.

You can use it to store:

A website can forget things when you refresh the page.

A database remembers.

That is its job.

PostgreSQL is very good at remembering things.

Sometimes too good.

Delete carefully.

What Is a Database?

A database is a place where data is stored in an organized way.

Imagine a database like a digital cabinet.

Inside the cabinet, you have tables.

Each table stores one type of information.

For example:

Database: online_shop

Tables:
  users
  products
  orders

The users table stores users.

The products table stores products.

The orders table stores orders.

Simple idea.

Very powerful.

Like boxes in a garage.

Except PostgreSQL does not forget where it put the screwdriver.

Usually.

What Is a Table?

A table stores data in rows and columns.

Example:

users table

id | name   | email
---|--------|------------------
1  | Anna   | anna@example.com
2  | Marco  | marco@example.com
3  | Viktor | viktor@example.com

Each column describes a type of data.

For example:

Each row is one record.

One user.

One product.

One order.

A table is like a spreadsheet.

But stronger.

More structured.

Less likely to be destroyed by someone accidentally dragging a cell at 2 AM.

What Is SQL?

SQL means Structured Query Language.

It is the language we use to talk to databases.

With SQL, we can say:

SELECT * FROM users;

This means:

Give me all users.

We can also say:

INSERT INTO users (name, email)
VALUES ('Anna', 'anna@example.com');

This means:

Add a new user.

SQL is not JavaScript.

SQL has its own style.

Its own rules.

Its own drama.

But it is not impossible.

You will learn it step by step.

SQL is like giving instructions to a very serious librarian.

If your instruction is clear, the librarian gives you the data.

If your instruction is wrong, the librarian silently judges you with an error message.

Install PostgreSQL

You need PostgreSQL installed on your system.

Choose the command for your Linux distribution.

Arch Linux

sudo pacman -S postgresql

Initialize the database cluster:

sudo -iu postgres initdb -D /var/lib/postgres/data

Start PostgreSQL:

sudo systemctl enable --now postgresql

Check status:

systemctl status postgresql

If you see active (running), good.

PostgreSQL is alive.

Probably judging your SQL already.

Ubuntu or Debian

sudo apt update
sudo apt install postgresql postgresql-contrib

Start PostgreSQL:

sudo systemctl enable --now postgresql

Check status:

systemctl status postgresql

Fedora

sudo dnf install postgresql-server postgresql-contrib

Initialize PostgreSQL:

sudo postgresql-setup --initdb

Start PostgreSQL:

sudo systemctl enable --now postgresql

Check status:

systemctl status postgresql

Good.

Now PostgreSQL should be running.

If not, do not panic.

Database installation sometimes behaves like a cat entering a box.

It should work.

But it may need encouragement.

Open PostgreSQL with psql

PostgreSQL comes with a terminal tool called psql.

psql lets you talk directly to PostgreSQL.

Open it with:

sudo -iu postgres psql

You should see something like:

postgres=#

This means you are inside the PostgreSQL shell.

Congratulations.

You are now speaking directly to the database.

Use your powers responsibly.

To exit psql, type:

\q

Important:

PostgreSQL commands often need a semicolon:

SELECT version();

But special psql commands like \q, \l, and \dt do not need semicolons.

Yes.

There are two kinds of commands.

Because databases enjoy keeping beginners alert.

Check PostgreSQL Version

Inside psql, run:

SELECT version();

You should see information about your PostgreSQL version.

This confirms that PostgreSQL is working.

You just asked the database:

Who are you?

And it answered.

Politely.

With too much information.

List Databases

Inside psql, run:

\l

This lists databases.

You may see databases like:

postgres
template0
template1

These are default databases.

Do not delete them.

They are important.

PostgreSQL created them for system reasons.

When PostgreSQL creates something called template0, just leave it alone.

It knows things.

Create Your First Database

Now create your first database.

Inside psql, run:

CREATE DATABASE learning_postgresql;

Do not forget the semicolon.

If everything works, PostgreSQL says:

CREATE DATABASE

Now list databases again:

\l

You should see:

learning_postgresql

Congratulations.

You created a database.

A tiny kingdom for your data.

No castle yet.

But we have land.

Connect to the Database

To connect to your new database, run:

\c learning_postgresql

You should see something like:

You are now connected to database "learning_postgresql"

Now your prompt may change:

learning_postgresql=#

This means you are working inside your new database.

Good.

Be careful.

You are now operating inside your own data kingdom.

Small kingdom.

Still counts.

Create Your First Table

Now create a table named students.

Run this:

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  age INTEGER,
  email VARCHAR(150)
);

This creates a table with four columns:

Let us understand this.

id SERIAL PRIMARY KEY

This creates an auto-incrementing ID.

PostgreSQL will automatically give each row a number.

name VARCHAR(100) NOT NULL

This creates a text column for the student name.

NOT NULL means the name is required.

No empty mysterious students.

age INTEGER

This creates a number column.

email VARCHAR(150)

This creates a text column for email.

Good.

We now have a table.

The database cabinet has its first drawer.

Very emotional.

For databases.

Show Tables

To show tables in the current database, run:

\dt

You should see:

students

If you see it, good.

Your table exists.

If you do not see it, check:

PostgreSQL usually tells you what went wrong.

Not always nicely.

But it tells you.

Insert Data

Now let us add students.

Run:

INSERT INTO students (name, age, email)
VALUES ('Anna', 22, 'anna@example.com');

PostgreSQL should say:

INSERT 0 1

This means one row was inserted.

Now add more students:

INSERT INTO students (name, age, email)
VALUES ('Marco', 28, 'marco@example.com');

INSERT INTO students (name, age, email)
VALUES ('Viktor', 33, 'viktor@example.com');

Now we have data.

Real data.

Not much data.

But enough to make the database feel useful.

Like giving a notebook its first page.

Read Data with SELECT

To read all students, run:

SELECT * FROM students;

You should see something like:

id |  name  | age |       email
---+--------+-----+-------------------
1  | Anna   | 22  | anna@example.com
2  | Marco  | 28  | marco@example.com
3  | Viktor | 33  | viktor@example.com

This is your first SQL query.

Let us understand it.

SELECT

means:

Get data.

*

means:

All columns.

FROM students

means:

From the students table.

So:

SELECT * FROM students;

means:

Get all columns from all students.

Simple.

Powerful.

A little too powerful sometimes.

In real applications, you often avoid SELECT * when you do not need every column.

But for learning, it is perfect.

Select Specific Columns

You can select only some columns:

SELECT name, email FROM students;

Output:

 name  |       email
-------+-------------------
 Anna  | anna@example.com
 Marco | marco@example.com
 Viktor| viktor@example.com

This is useful when you do not need all data.

Maybe you need names and emails.

Not ages.

So you ask only for names and emails.

PostgreSQL appreciates precise requests.

So do humans.

Usually.

SQL Keywords

You may notice SQL keywords are written in uppercase:

SELECT * FROM students;

But this also works:

select * from students;

SQL is mostly case-insensitive for keywords.

But writing keywords in uppercase makes queries easier to read.

So in this course, we will write:

SELECT
FROM
WHERE
INSERT
UPDATE
DELETE

This is not required.

But it is a good habit.

Good habits make future debugging less painful.

Not painless.

Less painful.

Semicolons Matter

In SQL, commands usually end with a semicolon:

SELECT * FROM students;

If you forget the semicolon, psql waits for more input.

Example:

SELECT * FROM students

Then the prompt may change and wait.

You can finish the command by typing:

;

This is a common beginner moment.

You think PostgreSQL is frozen.

But it is just waiting.

Like a waiter who refuses to leave because you did not finish the sentence.

Useful psql Commands

Here are some useful psql commands:

\l

List databases.

\c database_name

Connect to a database.

\dt

Show tables.

\d table_name

Show table structure.

Example:

\d students

\q

Quit psql.

These commands are not SQL.

They are special psql commands.

That is why they start with \.

The backslash is your terminal wizard wand.

A very small wand.

Check Table Structure

Run:

\d students

You should see the structure of the table.

It will show columns, types, and constraints.

You may see something like:

id    | integer
name  | character varying(100)
age   | integer
email | character varying(150)

This helps you inspect your table.

Very useful when you forget what you created.

And you will forget.

Everyone forgets.

Databases remember.

Humans pretend.

Common Mistakes

Forgetting the Semicolon

Wrong:

SELECT * FROM students

Correct:

SELECT * FROM students;

SQL commands usually need ;.

Small symbol.

Big importance.

Like the last screw in a shelf.

Not Connecting to the Correct Database

You may create a table in one database and then look for it in another.

Example:

\c postgres
\dt

You might not see your students table.

Why?

Because you created it inside:

learning_postgresql

Connect back:

\c learning_postgresql
\dt

Now the table appears.

Databases are separate worlds.

Do not search for your socks in your neighbor's house.

Confusing SQL and psql Commands

This is SQL:

SELECT * FROM students;

This is a psql command:

\dt

SQL commands usually end with ;.

psql commands usually do not.

Different tools.

Same terminal.

Slight confusion.

Normal.

Forgetting Quotes Around Text

Wrong:

INSERT INTO students (name, age, email)
VALUES (Anna, 22, anna@example.com);

Correct:

INSERT INTO students (name, age, email)
VALUES ('Anna', 22, 'anna@example.com');

Text values need single quotes.

Numbers do not.

PostgreSQL is strict.

Like an accountant with a keyboard.

Practice

Create a new table named books.

It should have:

Example:

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  author VARCHAR(100),
  year_published INTEGER
);

Insert three books:

INSERT INTO books (title, author, year_published)
VALUES ('Clean Code', 'Robert C. Martin', 2008);

Then read all books:

SELECT * FROM books;

Then read only titles:

SELECT title FROM books;

This will help you practice:

Practice matters.

PostgreSQL does not enter the brain through the eyes only.

You need fingers.

Keyboard.

Mistakes.

And maybe coffee.

Mini Challenge

Create a database named:

personal_library

Inside it, create a table named:

books

The table should store:

Add at least five books.

Then run:

SELECT * FROM books;

Bonus:

Select only:

title, author

This is simple.

But it is already a real database structure.

Small database.

Real concept.

Big step.

Summary

Today you learned:

This is a strong beginning.

You now have PostgreSQL installed.

You created a database.

You created a table.

You inserted data.

You selected data.

That is already the core rhythm of databases:

Create structure.

Put data in.

Read data out.

Everything else builds on this.

Very slowly.

Very powerfully.

Very PostgreSQL.

Next Lesson

In the next lesson, we will learn more about databases and tables.

We will look deeper at:

Because a bad table design is like a messy garage.

At first it looks fine.

Then one day you need one cable.

And everything collapses.