Wprowadzenie do PostgreSQL

Covered topics:

Installing PostgreSQL

• Connection to the database
• Usage of pgAdmin4 and similar tools

Relational data models

• Normalization
• Creating tables and using data types
• Primary Keys and Foreign Keys
• Check constraints

Basic SQL commands

• INSERT: Insert data
• SELECT: Simple queries
• UPDATE: Modify data
• DELETE and SELECT: Delete data
• Filter data
• NULL Handling
• Sort data with ORDER BY
• LIKE queries
• UNION ALL, UNION, EXCEPT
• DISTINCT and DISTINCT ON
• LIMIT and OFFSET

Aggregate functions

• Aggregate data (count, sum, etc.)
• Minimum and maximum values
• Group data (GROUP BY … HAVING …)
• Use ordered sets (WITHIN GROUP …)

Subqueries and CTEs

• Nested queries
• Common Table Expressions (CTEs)
• Use IN and NOT IN
• EXISTS and NOT EXISTS

Joins

• Understand inner joins
• Use outer joins
• Semi joins, anti joins

Date

• Calculating with date and time
• Understanding intervals
• Include range types

Windowing and Analytics

• OVER-clauses
• Partitioning
• Define sliding windows
• Important analytical functions (rank, lag, etc.)
• Time series analysis

Views and materialized views

• Basic views
• Dependency handling
• Using materialized views

Transactions

• BEGIN, ROLLBACK
• Row locking and table locking
• Transaction isolation levels (read committed, etc)
• SELECT … FOR UPDATE
• Deadlocks

Cursor

• Declare cursor
• WITH HOLD cursor

Indexing

• Creating and deleting indexes
• Identify missing indexes
• Avoiding unnecessary indexes