PostgreSQL is a powerful, open-source object-relational database management system that emphasizes extensibility, data integrity, and high performance. Its versatility and robust feature set make it an ideal choice for developers working on projects of all sizes, from small-scale applications to enterprise-level systems. By learning PostgreSQL, developers can tap into its advanced capabilities, such as full-text search, spatial data support, and customizable data types, allowing them to create efficient and scalable solutions tailored to their unique needs. PostgreSQL for Database Developers is a three-day hands-on course that explores the fundamentals of database management, covering everything from installation and management to advanced SQL functions. Designed for beginners and enthusiasts alike, this course will equip you with the knowledge and skills required to effectively harness the power of PostgreSQL in today's data-driven landscape.

starstarstarstarstar

* Actual course outline may vary depending on offering center. Contact your sales representative for more information.

Learning Objectives

Upon completing this course, database developers will be able to:
Design and implement efficient database schemas by employing normalization techniques, appropriate indexing strategies, and partitioning methods to optimize data storage and retrieval processes.
Develop advanced SQL queries, including joining multiple tables, utilizing subqueries, and aggregating data, to extract valuable insights and facilitate decision-making processes.
Implement stored procedures, functions, and triggers to automate common database tasks, enforce data integrity, and improve overall application performance.
Apply database performance tuning techniques, such as query optimization, index management, and transaction control, to ensure optimal resource usage and enhanced system responsiveness.
Integrate databases with various programming languages and platforms, enabling seamless data access and manipulation for web, mobile, and desktop applications.

1

  • INSTALLING & MANAGING POSTGRESQL

  • PostgreSQL installation process

    Optimal configuration settings

    User and role management

    Database backup and restoration

2

  • OVERVIEW OF POSTGRESQL DATABASE

  • PostgreSQL architecture overview

    Understanding database objects

    Efficient data storage

    Transaction management basics

3

  • USING THE PSQL CLIENT

  • Introduction to psql

    Essential psql commands

    Executing queries effectively

    Managing databases with psql

4

  • UNDERSTANDING POSTGRESQL DATA TYPES

  • Numeric data types explored

    Character and binary types

    Date, time, and boolean values

    Array and other types

5

  • UNDERSTANDING SEQUENCES

  • Sequence creation and usage

    Customizing sequence behavior

    Implementing auto-increment columns

    Sequence manipulation and control

6

  • CREATING & MANAGING INDEXES

  • PostgreSQL index fundamentals

    Designing partial indexes

    Utilizing expression-based indexes

    Index management techniques

7

  • USING COPY TO LOAD DATA

  • COPY command overview

    Importing and exporting data

    Handling CSV and binary formats

    Performance considerations

8

  • WORKING WITH ARRAY VALUES

  • Array value basics

    Array manipulation functions

    Querying arrays efficiently

    Multidimensional array handling

9

  • ADVANCED SQL FUNCTIONS

  • Mastering Common Table Expressions

    Utilizing Window Functions

    Regular Expressions in SQL

    Crafting Recursive Queries

10

  • WRITING TRIGGERS & STORED PROCEDURES WITH PL/PGSQL

  • PL/pgSQL variables usage

    Implementing loop operations

    PERFORM and EXECUTE statements

    Developing PostgreSQL triggers

11

  • USING THE POSTGRESQL QUERY OPTIMIZER

  • Query analysis and optimization

    EXPLAIN command insights

    PostgreSQL query operators

    Identifying performance bottlenecks

12

  • IMPROVING QUERY PERFORMANCE

  • Query performance tuning

    Index optimization strategies

    Efficient database partitioning

    Connection and resource management

13

  • WRAP UP & ADDITIONAL RESOURCES

  • Further learning opportunities

    Staying up-to-date with PostgreSQL

    Community engagement and support

Audience

The ideal audience for this course includes database enthusiasts, IT professionals, and developers who are eager to expand their knowledge and skill set in database management and optimization. Roles that would greatly benefit from attending this course include: Database Developers: Those who design, implement, and maintain databases as part of their primary responsibilities and want to improve their expertise in schema design, query optimization, and advanced database features. Backend Developers: Professionals who work on server-side application logic and require a strong understanding of database management to integrate data storage and retrieval processes into their applications.

Language

English

Prerequisites

To ensure a smooth learning experience and maximize the benefits of attending this course, you should have the following prerequisite skills: Basic SQL Knowledge: Familiarity with SQL syntax and concepts, such as SELECT, INSERT, UPDATE, and DELETE statements, as well as an understanding of relational databases and how they store and organize data. General Programming Experience: Competence in at least one programming language (e.g., Python, Java, C#, or JavaScript) to facilitate the understanding of database integration and automation through stored procedures, functions, and triggers.

$2,295

Length: 3.0 days (24 hours)

Level:

Not Your Location? Change

Course Schedule:

Schedule select
04
Dec
Monday
10:00 AM ET -
6:00 PM ET
Filling Fast
Available
Loading...