O'Reilly logo
live online training icon Live Online training

Getting started with PostgreSQL

Managing and querying PostgreSQL databases

Ami Levin

PostgreSQL has been the leading enterprise open source relational database management system for several decades. The latest version, PostgreSQL 12, provides an even more reliable, feature-rich, and easy-to-use platform to gain data insights.

Join expert Ami Levin to get up and running with PostgreSQL and gain experience with the basic skillset you’ll need to master SQL and relational databases. You’ll discover what PostgreSQL is, how it works, and the great tools that it offers as you learn how to create and manage databases and use basic SQL commands to manipulate and query data. Whether you’re starting your PostgreSQL journey or just want to improve your skills, this hands-on four-hour course will get you well on your way to performing basic management tasks and writing SQL queries.

What you'll learn-and how you can apply it

By the end of this live online course, you’ll understand:

  • PostgreSQL basic architecture and tooling
  • What’s new to PostgreSQL version 12
  • PostgreSQL query fundamentals

And you’ll be able to:

  • Set up and connect to local and cloud PostgreSQL environments
  • Basic management tasks such as creating and backing up databases
  • How to create and manage tables, insert and modify data, and query it efficiently

This training course is for you because...

  • You’re a developer, data analyst, or IT or data practitioner who wants to get started with PostgreSQL.
  • You need to be able to use and query PostgreSQL databases.

Prerequisites

  • Familiarity with SQL (useful but not required)

Recommended preparation:

Recommended follow-up:

About your instructor

  • Ami Levin is a seasoned trainer, data architect, database designer, and SQL wizard with over 25 years of experience.

Schedule

The timeframes are only estimates and may vary according to how the class is progressing

PostgreSQL architecture (20 minutes)

  • Lecture: PostgreSQL overview; system databases; the fundamental database file architecture; how to connect to a cloud PostgreSQL IaaS and PaaS
  • Demo: Installation walkthrough; connecting to local and cloud instances; using PgAdmin and PSQL
  • Hands-on exercises: Connect to a shared cloud PostgreSQL database and run a query

Creating and managing databases (15 minutes)

  • Lecture: How to create a new database; commonly used settings; database options; how to configure existing databases; how to perform basic management tasks such as backing up and exporting data
  • Demo: Creating and configuring databases
  • Hands-on exercises: Create and configure databases

Accessing PostgreSQL objects (20 minutes)

  • Lecture: The object hierarchy; how to access objects using fully qualified identifiers; how to use DCL, users, and roles to assign basic permissions
  • Demo: Creating users; assigning permissions
  • Hands-on exercises: Create users and assign permissions
  • Q&A

Break (5 minutes)

CREATE, ALTER, and DROP TABLE (15 minutes)

  • Lecture: Creating tables using SQL DDL statements; modifying existing tables and reclaiming space for unused data
  • Demo: Creating, modifying, and deleting tables
  • Hands-on exercises: Create, modify, and delete tables

Column and table constraints (20 minutes)

  • Lecture: Relational keys; how to enforce data consistency
  • Demo: Creating and modifying keys and constraints
  • Hands-on exercises: Create and modify constraints

Data types (20 minutes)

  • Lecture: PostgreSQL data type families and their proper usage; how to avoid common mistakes regarding data type selection
  • Demo: Data types
  • Hands-on exercises: Create and modify column types
  • Q&A

Break (5 minutes)

Using INSERT, UPDATE, and DELETE (15 minutes)

  • Lecture: Writing basic DML statements; PostgreSQL DML extensions
  • Demo: Using INSERT, UPDATE and DELETE
  • Hands-on exercises: Use INSERT, UPDATE and DELETE

Understanding SELECT and query processing order (10 minutes)

  • Lecture: How the SQL engine processes SELECT queries; writing efficient SQL queries; how to avoid common errors

SQL JOIN fundamentals (10 minutes)

  • Lecture: The various JOIN types and how they’re processed by PostgreSQL; CROSS, INNER, and OUTER; how to enforce JOIN order
  • Demo: Basic JOIN techniques
  • Hands-on exercises: Join two tables

Filtering, aggregating, and limiting result sets (20 minutes)

  • Lecture: How to write queries using WHERE, GROUP BY, HAVING, TOP, ORDER BY, and OFFSET-FETCH; how these clauses are processed; how to avoid common mistakes
  • Demo: Using WHERE, GROUP BY, and OFFSET-FETCH
  • Hands-on exercises: Use WHERE and GROUP BY
  • Q&A

Break (5 minutes)

PostgreSQL indexes (15 minutes)

  • Lecture: PostgreSQL index types; guidelines for efficient indexes; how to create indexes as part of table definition and for existing tables
  • Demo: Creating and using indexes
  • Hands-on exercises: Create an index

Commonly used operators and functions (20 minutes)

  • Lecture: How to use PostgreSQL’s most commonly used built-in operators and functions; writing queries using logical operators, numeric, string, and temporal functions
  • Demo: Using common operators and functions
  • Hands-on exercises: Write a query that use operators and functions

Views, stored procedures, triggers, and functions (20 minutes)

  • Lecture: The benefits and use cases for server-side programming objects; creating views, stored procedures, triggers, and both scalar and table valued functions
  • Demo: Creating and using programming objects
  • Hands-on exercises: Create a trigger

Wrap-up and Q&A (5 minutes)