Md Nazmus Sakib

Posted on Sep 2

PostgreSQL Tutorial & Real World Guide

#programming #refinehackathon #javascript #beginners

Made by: Md. Nazmus Sakib

Profession: Full Stack Developer

PostgreSQL Home
PostgreSQL Intro
PostgreSQL Install
PostgreSQL Get Started
PostgreSQL pgAdmin 4
DBeaver: Universal DB Client
Database Operations
Create Demo Database
PostgreSQL Syntax & Operators
PostgreSQL SELECT & Clauses
PostgreSQL Joins
Other Important Clauses
Prisma Integration
Resources

PostgreSQL Home

PostgreSQL is a powerful, open-source relational database system that’s trusted for robustness, scalability, and extensibility.

Used by companies like Apple, Instagram, and Reddit for storing millions of records and complex business logic.

PostgreSQL Intro

Relational Database: Stores data in tables with rows and columns.
Open Source: Free, community-driven, and highly extensible.
Real Life Example: Instagram uses PostgreSQL for user data, photos, likes, comments, and relationships.

PostgreSQL Install

Ubuntu:

sudo apt update
sudo apt install postgresql postgresql-contrib

macOS (Homebrew):

brew install postgresql

Windows:

Download installer from PostgreSQL Official Site

PostgreSQL Get Started

Start the PostgreSQL service:

sudo service postgresql start

Access the CLI:

sudo -u postgres psql

Create a database:

CREATE DATABASE shop;

Create a user:

CREATE USER shopuser WITH PASSWORD 'strongpassword';
GRANT ALL PRIVILEGES ON DATABASE shop TO shopuser;

PostgreSQL pgAdmin 4

pgAdmin 4 is the official graphical user interface for PostgreSQL.

It allows you to manage databases, run queries, view statistics, and more — all via a web browser.

DBeaver: Universal DB Client

DBeaver is a powerful database management tool supporting PostgreSQL and many other databases.

Use it for visual table design, ER diagrams, queries, and data analysis.
Real Life: Data analysts use DBeaver to explore customer purchase histories and sales trends from PostgreSQL.

Database Operations

PostgreSQL CREATE TABLE

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Real Life: E-commerce platforms store customer info.

PostgreSQL INSERT INTO

INSERT INTO customers (name, email) VALUES ('Sakib', 'sakib@gmail.com');

Real Life: Registering new users on a website.

PostgreSQL Fetch Data

SELECT * FROM customers;

Real Life: Admin dashboard shows all customers.

PostgreSQL ADD COLUMN

ALTER TABLE customers ADD COLUMN phone VARCHAR(20);

Real Life: Adding mobile number support.

PostgreSQL UPDATE

UPDATE customers SET email = 'sakib@outlook.com' WHERE id = 1;

Real Life: User updates their email address.

PostgreSQL ALTER COLUMN

ALTER TABLE customers ALTER COLUMN phone TYPE VARCHAR(30);

Real Life: Allow longer phone numbers.

PostgreSQL DROP COLUMN

ALTER TABLE customers DROP COLUMN phone;

Real Life: Remove unnecessary data fields.

PostgreSQL DELETE

DELETE FROM customers WHERE id = 1;

Real Life: GDPR request to delete user data.

PostgreSQL DROP TABLE

DROP TABLE customers;

Real Life: Remove legacy tables after migration.

Create Demo Database

CREATE DATABASE demo;
-- Connect to demo
\c demo

-- Create table
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  title VARCHAR(100),
  price NUMERIC
);

INSERT INTO products (title, price) VALUES ('Laptop', 1200), ('Mouse', 25);

Real Life: Inventory management for an electronics shop.

PostgreSQL Syntax & Operators

Syntax

SELECT column1, column2 FROM table_name WHERE condition;

Operators

= (equal), <> (not equal), <, >, <=, >=
AND, OR, NOT
IN, BETWEEN, LIKE

SELECT * FROM products WHERE price > 100 AND title LIKE '%top%';

PostgreSQL SELECT & Clauses

PostgreSQL SELECT

SELECT * FROM products;

SELECT DISTINCT

SELECT DISTINCT title FROM products;

WHERE

SELECT * FROM products WHERE price > 500;

ORDER BY

SELECT * FROM products ORDER BY price DESC;

LIMIT

SELECT * FROM products LIMIT 5;

MIN and MAX

SELECT MIN(price), MAX(price) FROM products;

COUNT

SELECT COUNT(*) FROM products WHERE price > 500;

SUM

SELECT SUM(price) FROM products;

AVG

SELECT AVG(price) FROM products;

LIKE

SELECT * FROM products WHERE title LIKE 'Lap%';

IN

SELECT * FROM products WHERE price IN (25, 1200);

BETWEEN

SELECT * FROM products WHERE price BETWEEN 100 AND 1300;

AS (Alias)

SELECT title AS "Product Name", price AS "Cost" FROM products;

PostgreSQL Joins

Suppose you have tables orders and customers.

INNER JOIN

SELECT orders.id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

LEFT JOIN

SELECT orders.id, customers.name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.id;

RIGHT JOIN

SELECT orders.id, customers.name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.id;

FULL JOIN

SELECT orders.id, customers.name
FROM orders
FULL JOIN customers ON orders.customer_id = customers.id;

CROSS JOIN

SELECT customers.name, products.title
FROM customers
CROSS JOIN products;

UNION

SELECT name FROM customers
UNION
SELECT title FROM products;

GROUP BY

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

HAVING

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

EXISTS

SELECT name FROM customers WHERE EXISTS (
  SELECT 1 FROM orders WHERE orders.customer_id = customers.id
);

ANY

SELECT * FROM products WHERE price = ANY (SELECT price FROM products WHERE price < 100);

ALL

SELECT * FROM products WHERE price > ALL (SELECT price FROM products WHERE price < 1000);

CASE

SELECT
  title,
  price,
  CASE 
    WHEN price > 1000 THEN 'Expensive'
    ELSE 'Affordable'
  END AS category
FROM products;

Prisma Integration with PostgreSQL

Prisma is a next-generation Node.js/TypeScript ORM for PostgreSQL (and more).

It makes database access safe, fast, and easy.

How Prisma Works

Define your DB schema in schema.prisma
Generate type-safe client code
Write database queries in JavaScript/TypeScript

Install & Set Up

npm install @prisma/client
npx prisma init

Add your PostgreSQL URL in .env:

DATABASE_URL="postgresql://shopuser:strongpassword@localhost:5432/shop"

Example Prisma Schema

model Customer {
  id      Int     @id @default(autoincrement())
  name    String
  email   String  @unique
  orders  Order[]
}

model Order {
  id          Int      @id @default(autoincrement())
  product     String
  customerId  Int
  customer    Customer @relation(fields: [customerId], references: [id])
}

Migration

npx prisma migrate dev --name init

Query Examples

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()

// Create a customer
await prisma.customer.create({
  data: { name: 'Sakib', email: 'sakib@gmail.com' }
})

// Find all customers
const customers = await prisma.customer.findMany()

// Create an order for a customer
await prisma.order.create({
  data: { product: 'Laptop', customerId: 1 }
})

Real World Use Cases

E-commerce: Customers, Orders, Products — with relations and constraints.
Blog/CMS: Users, Posts, Comments — with easy querying and filtering.
Analytics: Store logs/events as JSONB, aggregate with SQL and Prisma.
Healthcare: Patients, Doctors, Appointments, secure medical records.
Education: Students, Courses, Enrollments, track progress.

Resources

PostgreSQL and Prisma together empower you to build scalable, high-performance, and secure applications for any real world need!

Table of Contents