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 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