PostgreSQL intensive

OBJECTIVES

• Learn to install and configure PostgreSQL in different environments.
• Manage advanced data structures and use PostgreSQL-specific data types.
• Create and optimize advanced SQL queries, including subqueries and complex joins.
• Implement and manage functions, stored procedures, and triggers.
• Administer security, user permissions, and perform maintenance and monitoring tasks.
• Use advanced extensions like PostGIS to extend PostgreSQL functionalities.

CURRICULUM

1. Introduction to PostgreSQL
   • History and features of PostgreSQL
   • Installation and initial configuration
   • Administration tools (pgAdmin, psql)
2. PostgreSQL Structure and Architecture
   • Database architecture
   • Data types in PostgreSQL
   • Creating and managing databases and tables
3. Basic SQL Commands in PostgreSQL
   • Basic SQL commands (SELECT, FROM, WHERE, GROUP BY, ORDER BY, INSERT, UPDATE, DELETE, CREATE)
   • PostgreSQL functions
   • Subqueries, unions, and joins
   • Error handling
4. Functions and Procedures
   • User-defined functions
   • Stored procedures
   • Triggers and rules
5. Indexing and Optimization
   • Types of indexes and their creation
   • Query optimization, ANALYZE
6. Database Administration
   • Backup and restore (pg_dump and pg_restore)
   • Monitoring and maintenance
   • User management ROLE
   • Permission management GRANT
7. Advanced Functions
   • Table partitioning
   • Replicas and high availability
   • Extensions and additional modules (PostGIS, etc.)