PostgreSQL: Data Modelling and Analytics

PostgreSQL is a powerful open-source database management system (DBMS) known for standards compliance and efficient handling of large-scale datasets. Beyond its core strengths, it offers exceptional support for diverse data types and extensibility.
  
In this hands-on workshop, we’ll start with PostgreSQL’s core capabilities for data modeling and analytical querying. You’ll learn to apply aggregates, window functions, and custom aggregation functions using languages like PL/pgSQL, Python, Perl, C, R, or Lua. We’ll also explore indexing strategies for fast retrieval, including full-text search and trigram-based fuzzy matching on large textual datasets.

Allgemeine Informationen

Dauer 12 hours
This workshop will explore foundational analytics capabilities in PostgreSQL, including: 
  • Data modelling and data types 
  • Functionalities for data analytics 
  • Smart indexing for fast data retrieval 
What requirements must participants fulfil? 
  • Basic understanding of SQL (SELECT, FROM, WHERE, GROUP BY, JOINs). 
  • Some previous contact with relational databases is advantageous.
  • Participants should bring their own laptops with PostgreSQL installed (links will be provided upfront).
This course is particularly aimed at: 
  • Researchers and assistants who need to manage and analyse large or complex datasets. 
  • IT staff to provide technical solutions for research projects. 
  • Data analysts and scientists looking to leverage the advanced analytical capabilities of PostgreSQL. 
  • Software engineers and developers working with applications that rely on PostgreSQL for data storage and complex querying or wanting to deepen their understanding of PostgreSQL's extensibility and specialized features. 
Upon completion of this workshop, participants will be able to: 
  • design effective data models in PostgreSQL by aligning structures and indexes with use cases and data types. 
  • leverage PostgreSQL’s advanced text search capabilities to perform efficient, language-aware and fuzzy text queries for data analysis. 
  • extract meaningful insights and patterns from complex datasets by formulating and executing advanced analytical queries.
  • A dedicated GitLab repository or OLAT course page containing all lecture slides (PDF format).
  • Sample datasets used for exercises and demonstrations.
  • Scripts with solutions for all coding exercises.
  • A curated list of links to official PostgreSQL documentation and relevant online resources for further learning. ​​​​​​
This workshop will be highly interactive and hands-on. The learning content will be conveyed through: 
  • Short, focused lectures introducing key concepts and PostgreSQL features. 
  • Live demonstrations showcasing the practical application of these features. 
  • Guided coding exercises with practical case studies where participants will develop queries to solve specific problems. 
  • This workshop focuses on practical application and hands-on experience. Participants are encouraged to actively engage and ask questions.
  • Participants will leave with practical skills that can be immediately applied to their own data analysis projects. ​​​​

Kursdaten

Code Referierende Daten Plätze frei Ort
HS25-PSQL-01 Graën Johannes 07.01.2026 - 28.01.2026 (14:00 - 17:00 Uhr)
Universität Zürich Irchel Die Kursanmeldung beginnt am 1. Februar für das Frühjahrssemester und am 1. September für das Herbstsemester.