Description
This training is intended for those who want to go further in the use of PostgreSQL. This database management system is one of the most advanced in implementing the SQL standard. It allows you to apply the latest specifications such as, for example, the WINDOW and PARTITION BY directives.
Who is this training for ?
For whom ?
This course is aimed at developers wishing to have in-depth knowledge of the possibilities offered by PostgreSQL.
Prerequisites
Good knowledge of SQL language and PostgreSQL.
Training objectives
Training program
- Reminders
- Server and access configuration.
- The command line and graphical client.
- Exercise
- Creating databases, schemas and data types
- Choice of template and charset. The search_path.
- Predefined types (monetary...) and table types.
- Domains and user types.
- Definition of new basic types in C language.
- Exercise
- Creating tables and views
- Foreign key constraints.
- Inherited tables (EXTENDS). Partitioned tables.
- Indexing. Materialized views.
- Practical work
- Implementing complex queries
- Aggregates, joins and subqueries.
- WINDOW and PARTITION BY clauses.
- Search in FULL TEXT mode.
- Query analysis with EXPLAIN.
- Exercise
- Transaction management
- Transaction management
- Crosspoints. Isolation levels.
- Explicit row and table locks.
- Informational locks for applications.
- Exercise
- Management of users, roles and rights
- Users and roles.
- Role inheritance, organization into groups.
- PUBLIC role. Rights.
- Exercise
- Implementing stored procedures
- Procedures written in PL/pgSQL in Perl and C.
- Cursors. Definition of operators.
- Setting up triggers.
- Exercise
- Client programming interfaces
- Programming in C, Perl and PHP.
- Retrieval of graphic elements from the database.
- Processing of SQL transactions using exceptions.
- Exercise
