Description

The PostgreSQL DBMS has the majority of features that can be found in commercial products like Oracle or DB2. This training will teach you how to use it effectively, in particular how to create and operate a database, or how to program with the PL/pgSQL language.

Who is this training for ?

Developers, operators and anyone else wishing to make the best use of PostgreSQL or migrate to PostgreSQL.

Training objectives

Training program

• Introduction
  • PostgreSQL presentation, versions.
  • The relational model, the schema.
  • Tables, constraints.
  • User interfaces.
• SQL avec PostgreSQL
  • The SQL standard.
  • Positioning PostgreSQL.
  • Setting up a database schema.
  • PostgreSQL data type.
  • Tables.
  • Sequences.
  • Views.
  • Integrity constraints on tables.
  • Addition , modification and deletion of data.
  • Querying the schema of a database.
  • Selection, restriction, sorting, joining.
  • Complex extractions.
  • Using operators.
  • PostgreSQL SQL specificities.
  • Advanced PostgreSQL features.
  • Transactions.
  • Views.
  • Tables.
  • Table inheritance.
  • Practical work Creating a schema.
  • SQL DDL script.
  • Creating views.
  • Transaction scenarios.
• Le langage PL/pgSQL
  • Instructions, types of the language.
  • Code blocks.
  • Control structures.
  • Errors and exceptions.
  • Cursors.
  • Declare, move and close.
  • Procedures and functions.
  • Adding subroutines to a database.
  • Triggers, triggers.
  • Structure.
  • Practical work Stored functions.
• Data maintenance
  • Import/export data.
  • With COPY TO and COPY FROM.
  • With Psql.
  • Backup and restore data.
  • pg_dump tools.
  • Query optimization.
  • Indexes.
  • The optimizer.
  • EXPLAIN and VACUUM.
  • Practical work Importing/exporting data.
  • Creating indexes and analyzing query execution plans.
• User and group management
  • Creation.
  • Authentication.
  • Granting privileges.
  • Practical work Creation of users, allocation of rights.
  • Authentication tests.
• Configuration de PostgreSQL
  • Installing and starting a database.
  • The Postmaster.
  • The Postgresql file.
  • conf.
  • System tables.
  • Practical work Installation of a base and configuration.
• Supplements
  • Programming interfaces.
  • Connecting PostgreSQL with PHP, JDBC, ODBC.