1
Introduction
- Overview of PostgreSQL, versions.
- The relational model, the schema.
- Tables, constraints. User interfaces.
2
SQL with PostgreSQL
- The SQL standard. Positioning of PostgreSQL. Setting up a database's schema.
- PostgreSQL data type. Tables. Sequences. Views. Integrity constraints on tables.
- Adding, editing, and deleting data.
- Querying a database's schema. Selection, restriction, sorting, joining.
- Complex queries. Using operators. SQL features of PostgreSQL.
- Advanced features of PostgreSQL. Transactions. Views. Tables. Table inheritance.
Hands-on work
Creating a schema. SQL DDL scripts. Creating views. Transaction scenarios.
3
PL/pgSQL language
- Instructions, types of language. Blocks of code.
- Control structures. Errors and exceptions.
- Cursors. Declaration, movement, and closing.
- Procedures and functions.
- Adding subprograms to a database.
- Triggers. Structure.
Hands-on work
Stored functions.
4
Data maintenance
- Importing/exporting data.
- With COPY TO and COPY FROM. With Psql.
- Backing up and restoring data.
- pg_dump tools. Request optimization
- Indexes. The optimizer. EXPLAIN and VACUUM.
Hands-on work
Importing/exporting data. Creating an index and analyzing a query execution plan.
5
Managing users and groups.
- Creation. Authentication.
- Grant privileges.
Hands-on work
Creating users, allocating rights. Authentication tests.
6
Configuring PostgreSQL
- Installing and starting a database.
- Postmaster. The Postgresql.conf file.
- System tables.
Hands-on work
Installing a database and configuration.
7
Additional instruction
- Programming interfaces.
- Connecting PostgreSQL with PHP, JDBC, ODBC.
3d
- 21h00 
Download in PDF format
Share this course by email