PostgreSQL Overview and Basics

Overview

This lecture introduces PostgreSQL, a widely used open-source relational database. It covers fundamentals of databases, SQL operations, table creation, data manipulation, constraints, joins, sequences, and exporting data.

Introduction to PostgreSQL

• PostgreSQL is a popular, open-source, robust, and high-performance relational database.
• Learning PostgreSQL is valuable for backend software development.
• The course uses the command-line interface (psql) instead of graphical tools for a deeper understanding.

Databases and SQL Basics

• A database stores, retrieves, manipulates, and deletes data, typically on a server.
• Data is structured in tables, consisting of rows and columns.
• SQL (Structured Query Language) is used to manage and query relational databases.
• Tables can have relationships (relational databases), connecting data using keys.

Installing and Connecting to PostgreSQL

• Instructions are provided for setting up PostgreSQL on both Mac and Windows.
• Main ways to connect: GUI clients (like pgAdmin), psql shell, and applications.
• psql shell is preferred for learning and remote server access.

Creating and Managing Databases and Tables

• CREATE DATABASE [name]; creates a new database.
• DROP DATABASE [name]; deletes a database irreversibly.
• CREATE TABLE defines columns, data types, and constraints.
• Table columns should be designed with appropriate types (e.g., INT, VARCHAR, DATE).

Constraints and Keys

• Constraints enforce data integrity (e.g., NOT NULL, UNIQUE, PRIMARY KEY).
• PRIMARY KEY uniquely identifies rows.
• Unique constraints ensure each value in a column is unique.
• CHECK constraints enforce conditions (e.g., gender='male' OR gender='female').

Basic Data Operations (CRUD)

• INSERT INTO [table] (cols) VALUES (vals); adds new records.
• SELECT [cols] FROM [table]; retrieves data. Use SELECT * for all columns.
• WHERE filters query results based on conditions.
• UPDATE [table] SET col=val WHERE condition; modifies records.
• DELETE FROM [table] WHERE condition; removes records.*

Advanced Querying

• ORDER BY sorts query results (ASC or DESC).
• DISTINCT returns only unique values.
• LIMIT and OFFSET control how many rows are retrieved and from where.
• Aggregation functions: COUNT(), MAX(), MIN(), AVG(), SUM().
• GROUP BY groups results by a column for aggregation.
• HAVING filters aggregated results.
• LIKE and ILIKE (case-insensitive) search for patterns.
• BETWEEN selects data within a range.
• IN tests if a value matches any in a list.

Joins and Relationships

• FOREIGN KEY links a column to another table's primary key.
• INNER JOIN combines rows with matching keys in both tables.
• LEFT JOIN includes all rows from the left table and matches from the right, filling missing values with NULL.

Sequences and Serial IDs

• SERIAL/BIGSERIAL create auto-incrementing integer columns.
• Sequences manage auto-increment logic; can be reset or altered.

Data Export and Import

• Use \copy or \copy (SELECT ...) TO 'filename.csv' WITH CSV HEADER; to export query results as CSV.

Extensions and UUIDs

• Extensions add extra features to PostgreSQL.
• UUIDs (universally unique identifiers) can be used as primary keys for global uniqueness.
• CREATE EXTENSION IF NOT EXISTS allows loading extensions, such as uuid-ossp.

Handling Nulls and Errors

• COALESCE returns the first non-null value in a list.
• Use NULLIF to handle division by zero.
• ON CONFLICT in INSERT handles duplicates—can DO NOTHING or DO UPDATE.

Dates and Timestamps

• NOW() returns the current timestamp.
• INTERVAL can be used to add/subtract time units.
• EXTRACT retrieves parts (year, month, etc.) from dates.
• AGE calculates the difference between dates.

Key Terms & Definitions

• Primary Key — uniquely identifies each row in a table.
• Foreign Key — column that links to the primary key in another table.
• Constraint — rule enforced on data columns (e.g., NOT NULL, UNIQUE).
• SERIAL/BIGSERIAL — auto-incrementing integer columns managed by sequences.
• JOIN — SQL operation to combine rows from two or more tables.
• UUID — Universally Unique Identifier, ensures global uniqueness.
• COALESCE — SQL function returning the first non-null argument.
• ON CONFLICT — handles duplicates in INSERT operations.
• Aggregate Function — function that operates on sets of values, returning a single value (e.g., COUNT, AVG).

Action Items / Next Steps

• Practice creating, updating, and joining tables using psql.
• Review PostgreSQL documentation on data types and aggregate functions.
• Experiment with exporting data to CSV and using UUIDs as keys.
• Consider further study in backend development frameworks like Spring Boot or Node.js, and advanced PostgreSQL topics.