PostgreSQL Introduction

PostgreSQL is an advanced, free and open-source relational database system.

What is PostgreSQL?

PostgreSQL is a relational database management system
It stores data in tables and you work with it using SQL
It is open source, with a very permissive licence: free for any project
It began at the University of California, Berkeley, in 1986, and has been developed by a worldwide community ever since
New major versions come out every year

You will see it written as PostgreSQL, Postgres or PG. They all mean the same thing.

What Makes PostgreSQL Special?

Feature	What it gives you
Standards	Follows the SQL standard closely
Strict data	Refuses wrong data instead of silently changing it
JSONB	Store and search JSON documents inside a table
Arrays	A column can hold a list, like `{'Urdu','English'}`
`RETURNING`	Get back the rows you just inserted or changed
Extensions	Add features such as PostGIS (maps) or pgvector (AI search)
Concurrency	Many users can read and write at the same time without blocking each other

PostgreSQL's strictness is a good thing. If you try to put 'abc' into a number column, or a date like 2026-02-30, it stops you with an error, so bad data never gets in.

Where is PostgreSQL Used?

Web apps built with Django, Laravel, Ruby on Rails, Node.js and Next.js
Cloud services such as Supabase, Neon, Amazon RDS and Google Cloud SQL
Banking, maps (with PostGIS), analytics and AI apps (with pgvector)
Companies such as Instagram, Spotify and Reddit have used it

The Sample Database

This tutorial uses a small database called institute, with the same data as the SQL tutorial.

Example

sql

SELECT * FROM students;

Output

 id |       name      |    city   | age | marks | course_id
----+-----------------+-----------+-----+-------+-----------
  1 | Ali Khan        | Karachi   |  19 |    78 |         1
  2 | Ayesha Siddiqui | Lahore    |  21 |    85 |         2
  3 | Bilal Ahmed     | Karachi   |  22 |    64 |         1
  4 | Fatima Noor     | Islamabad |  20 |    92 |         3
  5 | Hamza Raza      | Karachi   |  18 |    70 |
  6 | Sana Iqbal      | Lahore    |  23 |    88 |         2
(6 rows)

In the psql client, NULL is shown as an empty space, as in Hamza's course_id.

Example

sql

SELECT * FROM courses;

Output

 id |       title        | duration_months
----+--------------------+-----------------
  1 | Web Development    |               6
  2 | Python Programming |               4
  3 | Graphic Designing  |               3
  4 | Digital Marketing  |               3
(4 rows)

students.course_id points to courses.id.

PostgreSQL and MySQL

Both are excellent free relational databases, and basic SQL is the same in both. A few differences:

	PostgreSQL	MySQL
Auto numbers	`GENERATED ALWAYS AS IDENTITY` or `SERIAL`	`AUTO_INCREMENT`
Join text	The double-pipe operator or `CONCAT`	`CONCAT`
Case-insensitive search	`ILIKE`	`LIKE` (usually already ignores case)
JSON	`JSON` and the faster `JSONB`	`JSON`
Get changed rows back	`RETURNING`	Not available

Exercise

What does PostgreSQL do if you try to store the text 'abc' in an INTEGER column?

Stores it as 0Stores it as text anywayRefuses it with an errorDeletes the column