Realdolmen Education

Details

PostgreSQL 9.x Querying

This course is currently not scheduled on the open calendar, but can be organized on request.

Request Course

Duration

3 day(s)

Audience

Everyone who has to access or develop a PostgreSQL database like analysts or even technical designers, database administrators and developers, client application developers and Business Inteligence professionals.

Prerequisites

Knowledge of relational database principles.

Objectives

After following this course, the participant will know how to create objects in a PostgreSQL database and query or modify data in a consistent and efficient way..

Methods

Instructor-led workshop with practical exercices.

Description

PostgreSQL is an open source database server which requires little or no maintenance and provides a very low total cost of ownership. Yet, it has proven to be an important and mature player in production environments. Together with the rise of its popularity, also the need for knowledge on this unique system has increased.

After a short introduction into the world and history of PostgreSQL, the database is uncovered and a realistic case is presented. The first part of the training will concentrate on data manipulation language, and more in particular the query. As compared to some other database systems, PostgreSQL has full support for the latest SQL ISO standard like Oracle or SQLserver, and so we dig thoroughly through all the syntax possibilities.

The second part of the course deals with data definition language. Not only will be explained how to create objects like tables, views, materialized views, sequences and indexes. We will also built a case to demonstrate the feature of table inheritance and table partitioning.

In the final modules we have a look at the PostgreSQL implementation of integrity constraints and some tips and tricks on transactions, locking and concurrency control.

Contents

  • Introduction to PostgreSQL
    • Brief history
    • Licensing and conventions
    • Installing client software
  • Clients and Connectivity
    • Connecting to PostgreSQL
    • Psql
    • pgAdminIII
  • Data Manipulation Language
    • Select clause
    • Where clause
    • Values lists
    • Limit and Offset
    • Order by
    • Group By and Having
    • Windowing functions (analytical)
    • Joins
    • Union, Intersect and Except
    • Subqueries
    • Common Table Expressions
    • Type conversion
    • Full Text Search
    • Insert, Update and Delete
    • Data-modifying statements in With clause
  • Data Definition Language
    • Metadata and the systems catalog
    • Tables
      • Creating tables
      • Table inheritance
      • Table partitioning
    • Views
    • Materialized views
    • Sequences
    • Indexes
  • Integrity
    • Primary key or OID
    • Unique key
    • Foreign key and referential integrity
    • Domains and check constraints
  • Performance and Concurrency
    • Transactions and MVCC
    • Transaction isolation and locking
    • Concurrency Control