PostgreSQL
Course Coordinator:
Sahil Kumar Jamwal
What is PostgreSQL?
● PostgreSQL (often called Postgres) is an open-source,
object-relational database management system (ORDBMS).
● It's been in active development since 1986 making it one of the
most mature and stable database systems available.
● Unlike relational databases PostgreSQL is "object-relational"
meaning it combines traditional relational database features with
object-oriented programming concepts.
● You get the structure and reliability of SQL databases with the
flexibility to extend and customize the system for your specific
needs.
Features That Set PostgreSQL Apart
1. JSONB Indexing
JSONB (Binary JSON) is a specialized data type that lets you store
data in a flexible, "key-value" format.
-- 1. Create the table
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
specifications JSONB -- This column holds our flexible data
);
Features That Set PostgreSQL Apart
-- 2. Insert data (Notice how the data looks like a Python dictionary
or JS object)
INSERT INTO products (name, specifications)
VALUES ('Laptop', '{"brand": "Dell", "ram": "16GB", "ports": ["USB-C",
"HDMI"]}');
-- 3. The "Arrow" Operator (->>)
-- Use this to grab a specific piece of text inside the JSON
SELECT name FROM products
WHERE specifications->>'brand' = 'Dell';
Features That Set PostgreSQL Apart
-- 4. The "Contains" Operator (@>)
-- Use this to check if a specific value exists inside a JSON array
SELECT name FROM products
WHERE specifications->'ports' @> '["USB-C"]';
Unlike standard JSON, the "B" stands for Binary meaning PostgreSQL
compresses the data and indexes it making searches lightning-fast.
It supports native indexing through GIN (Generalized Inverted Index)
and GiST indexes, making JSON queries incredibly fast. MySQL's JSON
type lacks this deep indexing capability.
Features That Set PostgreSQL Apart
-- Create a GIN index on the entire JSONB column
CREATE INDEX idx_specifications ON products USING GIN
(specifications);
-- Now queries become blazing fast, even with millions of rows
SELECT name FROM products
WHERE specifications @> '{"brand": "Dell"}';
-- This query uses the index instead of scanning every row!
Features That Set PostgreSQL Apart
2. Arrays
In traditional databases like MySQL, if a student has multiple phone
numbers you're forced to create a separate "Phones" table with
foreign keys. PostgreSQL gives you native arrays instead.
-- 1. Defining the Table
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
phone_numbers TEXT[], -- The [] brackets tell Postgres this is a list
of text
grades INTEGER[] -- A list of whole numbers );
Features That Set PostgreSQL Apart
-- 2. Inserting Data
-- You use the ARRAY[...] syntax to wrap your list
INSERT INTO students (name, phone_numbers, grades)
VALUES ('Alice', ARRAY['555-1234', '555-5678'], ARRAY[85, 90, 92]);
-- 3. Querying with ANY
-- Instead of looking for an exact match, we ask: "Is 85 inside this
list?"
SELECT name FROM students
WHERE 85 = ANY(grades);
Features That Set PostgreSQL Apart
3. ENUMs
Custom Types (specifically ENUMs) allow you to define a specific list
of allowed values.
ENUMs aren't unique to PostgreSQL, but PostgreSQL implements
them as true reusable types rather than just column constraints.
-- PostgreSQL: Define once, use everywhere
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
name VARCHAR(100),
current_mood mood
);
Features That Set PostgreSQL Apart
CREATE TABLE mood_log (
log_date DATE,
recorded_mood mood
);
Features That Set PostgreSQL Apart
4. Full-Text Search
● If you search for "running," you probably want to find results for
"run" and "ran" too.
● While MySQL has basic FULLTEXT indexing, PostgreSQL's full-text
search is far more advanced with better stemming, language
support and ranking capabilities.
● For serious search functionality, PostgreSQL eliminates the need
for external tools in most cases.
PostgreSQL uses two special tools to make this happen:
● tsvector: A list of "clean" words (lexemes) optimized for
searching.
● tsquery: The actual search term you are looking for.
Features That Set PostgreSQL Apart
-- 1. Create a table with some content
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title TEXT,
content TEXT
);
-- 2. Add some data
INSERT INTO posts (title, content)
VALUES ('Cooking Tips', 'I love baking bread and cakes in the
morning.');
Features That Set PostgreSQL Apart
-- 3. The search query
-- to_tsvector() turns the sentence into searchable tokens
-- to_tsquery() turns your search word into a format the database
understands. The @@ operator means "does this match?"
SELECT title
FROM posts
WHERE to_tsvector('english', content) @@ to_tsquery('english',
'bake');
Features That Set PostgreSQL Apart
5. Common Table Expressions (CTEs)
When you write a very long math problem, you usually solve one part,
write down the result, and then use it in the next step.
In SQL, a CTE (the WITH clause) allows you to do exactly that. It lets
you create a temporary "result table" that you can use in your main
query.
-- "Find the average grade, then show me students who scored
above it"
WITH average_calc AS (
SELECT AVG(score) as avg_score FROM test_results
)
Features That Set PostgreSQL Apart
SELECT student_name, score
FROM test_results, average_calc
WHERE score > average_calc.avg_score;
While MySQL now supports CTEs, PostgreSQL had them for over a
decade earlier and has more mature optimization.
Features That Set PostgreSQL Apart
6. Geospatial Data (PostGIS)
While most databases store simple text and numbers, PostgreSQL
can store physical locations (GPS coordinates) and shapes
(polygons). By adding the PostGIS extension, you can treat your
database like a map.
-- 1. Create a table for city locations
CREATE TABLE cities (
id SERIAL PRIMARY KEY,
name TEXT,
location GEOGRAPHY(POINT) -- Stores Longitude/Latitude
);
Features That Set PostgreSQL Apart
-- 2. Find cities within 50km of a specific point
SELECT name FROM cities
WHERE ST_DWithin(location, ST_MakePoint(-74.00, 40.71), 50000);
PostGIS turns PostgreSQL into a full Geographic Information System
(GIS). MySQL's spatial data types and functions but for any serious
location-based application PostGIS is the industry standard.
Features That Set PostgreSQL Apart
7. Foreign Data Wrappers (FDW)
PostgreSQL can act as a "hub" for all your data. Using Foreign Data
Wrappers, you can link external data sources (like a CSV file or a
different MySQL database) and query them as if they were local
PostgreSQL tables.
Even though it's not unique developers often prefer the PostgreSQL
implementation because there are wrappers for almost everything
mongo_fdw (MongoDB), redis_fdw (Redis), s3_fdw (Amazon S3), and
even twitter_fdw or google_fdw for APIs.
Features That Set PostgreSQL Apart
-- Link to a remote PostgreSQL server
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '[Link]', dbname 'external_db');
-- Query it like a normal table
SELECT * FROM remote_table_name;
Features That Set PostgreSQL Apart
8. Triggers
Triggers allow it to act as an automated "rule engine." A trigger is a
function that the database automatically runs whenever a specific
event occurs such as INSERT, UPDATE or DELETE.
The most critical difference is that in MySQL, you write the trigger
code directly inside the CREATE TRIGGER statement. In PostgreSQL,
you first create a standalone Function and then attach that function
to a Trigger. This "Object-Oriented" approach makes PostgreSQL
unique.
Features That Set PostgreSQL Apart
-- 1. Create the function to check the count
CREATE OR REPLACE FUNCTION check_crew_limit()
RETURNS TRIGGER AS $$
BEGIN
IF (SELECT COUNT(*) FROM film_crew WHERE studio_id =
NEW.studio_id) >= 10 THEN
RAISE EXCEPTION 'Studio % already has the maximum limit of 10
crews.', NEW.studio_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Features That Set PostgreSQL Apart
-- 2. Create the trigger to fire BEFORE INSERT
CREATE TRIGGER limit_crews_trigger
BEFORE INSERT ON film_crew
FOR EACH ROW
EXECUTE FUNCTION check_crew_limit();
Installing PostgreSQL on Ubuntu
Documentation link
# Import the repository signing key:
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/[Link] --fail
[Link]
# Create the repository configuration file:
. /etc/os-release
sudo sh -c "echo 'deb [signed-by=/usr/share/postgresql-common/pgdg/[Link]]
[Link] $VERSION_CODENAME-pgdg main' >
/etc/apt/[Link].d/[Link]"
# Update the package lists:
sudo apt update
Install PostgreSQL: (replace "18" by the version you want)
sudo apt install postgresql-18
Managing the PostgreSQL Service
Once installed, use these Linux terminal commands to control the
database background process.
sudo systemctl status postgresql:
Checks if the database is currently running or has errors.
sudo systemctl start postgresql:
Starts the PostgreSQL service if it is stopped.
sudo -u postgres psql :
Logs you into the PostgreSQL terminal (psql) as the default postgres
superuser.
Basic CLI Commands
\l: To see all databases on the server.
CREATE DATABASE vibemaster: This is used to create database.
\c vibemaster: Switch to your new database.
\dt: To see all tables in the current database.
\q: To exit the PostgreSQL terminal.
Program: 01
A film industry database needs to track movie studios and their
production crews.
movie_studios: Includes studio_id, name, branch and locations
(stored as an Array of text).
film_crews: Includes crew_id, name, studio_id and strength.
1. List the names and branches of all movie studios that currently
do not have any crews assigned to them.
2. Using a CTE identify the studio that employs the crew with the
highest strength.
3. Write a before insert trigger to check maximum number of crews
to any studio is limited to 3.
Program: 02
A university database needs to track student publications record
students: student_id, name, Phone_numbers (array of text), grades
(JSONB), publications (array of paper_id)
research_papers: paper_id, title, publication_date
1. List students with publications greater than 1.
2. List each student with their publication titles and dates using
CTE.
3. Using Trigger ensure no duplicate paper IDs are added to a
student’s publications.
THANK
YOU!