Overview of PostgreSQL Development
The Stalwart Learning Online PostgreSQL training course is ideal for those looking to migrate to PostgreSQL. This training course was designed for specifically for developers with SQL knowledge and experience and focuses on the features and functionality that are specific to PostgreSQL. Attendees will learn how to apply their SQL knowledge and experience to the PostgreSQL platform.
Designed for developers looking to migrate to PostgreSQL, this training course does not cover SQL, the SQL language, DML, or DDL – these are pre-requisites.
Duration
6 Half Days or 3 Full Days
Prerequisite for PostgreSQL Development
- Basic Computer Knowledge.
- Some understanding of programming Language and Database would be advantageous.
Lab Setup
HW : 1 Laptop per participants with 8GB RAM and 100 GB Disk Space
OS : Windows
SW : Software Installations would be part of the training
Course Outline for PostgreSQL Development
Installing & managing PostgreSQL
Overview of PostgreSQL database
Using the psql client
Understanding PostgreSQL data types
Numeric data types
- Integer data types
- Floating point types
- Serial types
Character types
Binary data types
Date & time values
Boolean values
Array types
Other data types
Understanding sequences
Creating & managing indexes
- Understanding PostgreSQL indexes
- Partial indexes
- Indexes on expressions
Using COPY to load data
Working with Array Values
Advanced SQL Functions
- Using Common Table Expressions (CTE)
- Using Window Functions
- Using Regular Expressions
- Using Recursive Queries
- Writing functions with PL/pgSQL
psql and scripting
Statement behaviour
Transaction Management & MVCC
Transaction Isolation and Visibility
Data loading
Optimizing PostGres Parameters
Security in Postgres
Users, Roles and Privileges
Client/Server topics
User defined functions and procedural languages
PL/pgSQL
Writing triggers & stored procedures with PL/pgSQL
- Variables in procedures
- Performing loop operations
- The PERFORM and EXECUTE statements
- Triggers in PostgreSQL
Using the PostgreSQL query optimizer
- Analyzing & optimizing queries
- Viewing execution plans with EXPLAIN
- Understanding PostgreSQL query operators