---

Prerequisites

• Basic knowledge of SQL
• Use of services and command-prompts in Windows
• The examples shown in the course will be run on a Windows 10 machine. The learner must be equipped with a Windows 10 laptop or a version of Ubuntu Linux.

---

Description

PostgreSQL is an advanced, enterprise-class, fully open source ORDBMS (Object-Relational Database Management). It is a relational database that supports SQL (relational) and JSON (non-relational) queries.

PostgreSQL is the most advanced open source database in the world! It supports numerous features including Multi-Version Concurrency Control (MVCC), point-in-time recovery, granular access controls, tablespaces, asynchronous replication, nested transactions, online/hot backup, a refined query scheduler and optimiser, and write ahead logging. PostgreSQL is highly scalable in both the amount of data it can handle and the number of concurrent users it can serve.

---

Contents

Introduction

• Installation
• Architectural basics
• Creating and accessing databases with psql.
• The (amazing) level of SQL support
• Creating tables and populating them with data
• Execute simple queries on tables
• Joining tables
• Aggregation Functions

Advanced Features

• Views
• Foreign Keys
• Transactions
• Window Functions
• Inheritance between tables

Definition of the data model

• Default values
• Auto-generated columns
• Constraints
• System columns
• Modifying the table structure
• Privileges
• Row security policies
• Patterns
• Inheritance
• Table partitioning
• Data from external sources
• Dependency tracking

Data manipulation

• Adding Data
• Updating data
• Delete data
• Returning data from changed rows

Queries

• Table Expressions
• Select Lists
• Ways to combine 2 or more queries
• Sort rows
• LIMIT and OFFSET
• VALUES Lists
• WITH Queries with CTE (Common Table Expressions)
• Recursive queries with CTE

Main data types

• Numeric, Monetary, Character
• Binary
• Date/Time
• Boolean
• Enumerated
• Geometric
• Bit String
• Text Search
• UUID Type
• JSON Types
• Arrays
• Composite Types
• Types Range
• OID Types

Functions and Operators

• Logical Operators
• Operators for pattern matching
• Functions for formatting output data
• Functions for date/time management
• Geometric functions and operators
• Functions for full-text-search
• Functions for unique identifiers
• Functions and operators for JSON
• Functions for sequence management
• Aggregation Functions
• Window functions
• Functions for system information
• Functions for system administration
• Trigger creation functions
• Statistical functions

Indexes

• Index types
• Multi-column indexes
• Indexes and ORDER BY
• Combining multiple indices
• Indexes on expressions
• Partial Indexes
• Index-Only Scans and Covering Indexes
• How is an index used?

Competition control

• Transaction Isolation
• Explicit Locking
• Locking and Indexes

Performance Tips

• Using and understanding EXPLAIN
• Statistics used by the Planner

Parallel Queries

• How parallel queries work
• When can parallel queries be used?

Client Authentication

• The file pg_hba.conf.
• Authentication types

Periodic maintenance tasks

• Vacuuming routine
• Routine Reindexing
• Log File Maintenance

Backup and Restore

• SQL Dump
• File System Level Backup
• Continuous Archiving and Point-in-Time Recovery (PITR)

Request information