28. PostgreSQL

BootCamp by Dr.Angela

1. Introduction to PostgreSQL

• Powerful, open-source relational database system widely used in the industry
• It offers strong community support, scalability, and excellent career opportunities
• Typical web application architecture : Client <-> Server <-> Application <-> Database
• Node.js + Postgres
  • pg is the official PostgreSQL client for Node.js
  • PostgreSQL runs as a separate database server
  • Tools like pgAdmin provide a GUI for database management

2. Tables, Keys, and PostgreSQL Data Types

• Table Structure : Columns (fields), Rows (records)
• Creating a Table : CREATE TABLE ( , , );
  • ex) CREATE TABLE friends ( id SERIAL PRIMARY KEY, name VARCHAR(50), age INT, is_cool BOOLEAN );
• PostgreSQL Data Types
  • SERIAL : Auto-incrementing integer
  • VARCHAR(n) : Limited-length text
  • TEXT : Unlimited text (including emojis)
  • INT : Integer values
  • BOOLEAN : True / False
• Reference : https://www.postgresql.org/docs/current/datatype.html

3. Creating Tables with pgAdmin

• pgAdmin allows visual database management
• CSV files can be imported into tables
• Ensure the CSV includes headers for proper mapping

4. Reading Data from PostgreSQL

• Basic SELECT Query : SELECT * FROM ;
• Node.js (pg Module) : ex) import pg from "pg";

const db = new pg.Client({
user: "postgres",
host: "localhost",
database: "world",
password: "123456",
port: 5432,
});

db.connect();

db.query("SELECT * FROM capitals", (err, res) => {
if (err) {
console.error("Error executing query", err.stack);
} else {
console.log(res.rows);
}
db.end();
});

5. SELECT, WHERE, and LIKE

• SELECT Variations :
  • SELECT * FROM table;
  • SELECT column FROM table;
  • SELECT column1, column2 FROM table;
• WHERE Clause : SELECT column FROM table WHERE condition;
  • Operators : =, >, <, >=, <=
• LIKE Operator : Used for pattern matching
  • ex) SELECT country FROM world_food WHERE country LIKE 'U%';
  • % : wildcard (matches any sequence of characters)
  • _ : single character wildcard

6. Constraints : UNIQUE and NOT NULL

• NOT NULL : Prevents empty values, Requires a value for the column
• UNIQUE : Ensures all values in a column are distinct, Prevents duplicates

7. INSERT Data

• SQL Insert Syntax : INSERT INTO table_name (column1, column2) VALUES (value1, value2);
• Node.js Example : db.query( "INSERT INTO world_food (country, rice_production, wheat_production) VALUES ($1, $2, $3)", ["Italy", 1.46, 7.3] );
  • $1, $2, $3 : parameterized queries (prevents SQL injection)

8. Advanced LIKE Queries

• ex) SELECT country_code FROM countries WHERE LOWER(country_name) LIKE '%' || $1 || '%';
  • LOWER() : makes search case-insensitive
  • || : string concatenation in SQL

9. One-to-One Relationships & INNER JOIN

• One-to-One Relationship : ex) CREATE TABLE student ( id SERIAL PRIMARY KEY, first_name TEXT, last_name TEXT );

CREATE TABLE contact_detail (
id INTEGER REFERENCES student(id) UNIQUE,
tel TEXT,
address TEXT
);

• Join : ex) SELECT * FROM student JOIN contact_detail ON student.id = contact_detail.id;

10. One-to-Many Relationships

• ex) CREATE TABLE homework_submission ( id SERIAL PRIMARY KEY, mark INTEGER, student_id INTEGER REFERENCES student(id) );
• Join Query : ex) SELECT * FROM student JOIN homework_submission ON student.id = student_id;
  • Selecting Specific Columns : ex) SELECT student.id, first_name, last_name, mark FROM student JOIN homework_submission ON student.id = student_id;

11. Many-to-Many Relationships & Aliases

• Junction Table : ex) CREATE TABLE class ( id SERIAL PRIMARY KEY, title VARCHAR(45) );

CREATE TABLE enrollment (
student_id INTEGER REFERENCES student(id),
class_id INTEGER REFERENCES class(id),
PRIMARY KEY (student_id, class_id)
);

• Many-to-Many Join : ex) SELECT * FROM enrollment JOIN student ON student.id = enrollment.student_id JOIN class ON class.id = enrollment.class_id;
• Using Aliases : ex) SELECT s.id AS stud, first_name, last_name, title FROM enrollment e JOIN student s ON s.id = e.student_id JOIN class c ON c.id = e.class_id;
  • AS : renames tables or columns for readability, Can be omitted for cleaner syntax

12. Updating and Deleting Data & Tables

• ALTER TABLE : ex)
  • ALTER TABLE student RENAME TO user;
  • ALTER TABLE user ALTER COLUMN first_name TYPE VARCHAR(20);
  • ALTER TABLE contact_detail ADD email TEXT;
• UNIQUE Constraint : ex) ALTER TABLE visited_countries ADD UNIQUE(user_id, country_code);
• DROP TABLE : DROP TABLE IF EXISTS table_name;
• UPDATE Data : UPDATE table_name SET column = value WHERE condition;
• ORDER BY : SELECT * FROM users ORDER BY id ASC;
• DELETE Data : DELETE FROM table_name WHERE condition;

13. Permalist Project

• Possible Enhancements : Sort tasks by creation date, Support multiple lists, Family/shared todo lists, Extend CRUD functionality, Improve UI/UX integration with database