CONTENUTI

Client Interfaces

• Command Line Interface – psql
• Graphical Interface - pgadmin4

 

Understanding PostgreSQL Data Types

• Numeric data types
• Character types
• Binary data types
• Date & time types
• Boolean values
• Array types
• Other data types

 

Retrieving information from tables using SELECT

 

Performing table joins

• Cross joins
• Inner joins
• Outer joins
• Natural joins

 

Restricting selections using the WHERE clause

 

Using ORDER BY to sort query results

 

Limiting rows returned using LIMIT

 

Computing summary values

• Using the GROUP BY clause
• Using the HAVING clause

 

Using UNION, INTERSECT and EXCEPT to join SELECT Results

 

Pattern matching using LIKE

 

Conditional expressions: The CASE statement, the COALESCE and NULLIF function

 

Subquery expressions

• The EXISTS expression
• The IN (and NOT IN) expression

 

Working with array values

 

Performing type casting and conversion

 

Understanding & managing sequence numbers

 

Creating tables (DDL)

• Using CREATE TABLE
• NOT NULL constraints
• PRIMARY KEY constraints
• FOREIGN KEY constraints

 

Modifying tables (ALTER TABLE)

 

Data manipulation language (DML)

• Inserting data
• Using COPY to perform data loads
• Using DELETE to remove rows
• Updating data using UPDATE

 

Transactions and Concurrency

• Transactions and Isolations
• Multi-Version Concurrency Control

 

Server Programming

• Extending SQL
• Triggers
• The Rule System
• Procedural Languages
• PL/pgSQL - SQL Procedural Language
• Error Handling
• Cursors

 

Foreign Data Wrappers

• Extension in PostgreSQL
• Adding FDW in a Database
• postgres_fdw
• file_fdw
• Other FDWs

 

SQL Tuning

• Logging in PostgreSQL
• Query Plans
• Optimizing Queries
• Statistics
• Planner Parameters
• Parallel Query Scans
• SQL Best Practices
• Indexes
• Table Partitioning