PostgreSQL Developer

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