Advanced PostgreSQL

Course details

PostgreSQL is a powerful, open-source, object-relational database system. It’s popular around the world, given its strong reputation for reliability, feature robustness, and performance. In this course, Janani Ravi dives into advanced topics in PostgreSQL. After showing how to install and set up PostgreSQL, Janani shows you how to work with geospatial data, which allows you to specify geographic objects such as locations, lines, and polygons, all on a geographic map, and also supports location-based queries like distance computation and area computation. Next, learn about performing full-text search operations, and how to work with triggers, which let you set up an automated execution of a function when a certain type of operation is performed. Finally, Janani explores transactions, which allow you to bundle multiple steps into a single, all-or-nothing operation.

Concepts

0. Introduction

01 - Advanced features in PostgreSQL

1. Installing and Setting up PostgreSQL

02 - macOS - Install PostgreSQL and PGAdmin 4
03 - macOS - Install and set up PostGIS
04 - Windows - Install PostgreSQL and PGAdmin 4
05 - Windows - Install and set up PostGIS

2. Working with Geospatial Data

06 - Spatial data analysis using PostGIS
07 - The WKT and WKB format
08 - Geometry and geography data types
09 - Spatial reference systems
10 - Creating a table to hold geometry types
11 - Working with basic geometry types
12 - Extracting coordinate values, computing length, and area
13 - Representing real-world data
14 - Computing the distance between locations
15 - Computing locations within a distance and representing lines on a map
16 - Importing shapefiles

3. Performing Full-Text Search Operations

17 - Full-text search
18 - Basic text search using LIKE and ILIKE
19 - Tsvector and tsquery
20 - Performing a full-text search using the @@ operator
21 - Using logical operations in search terms
22 - Performing a search using a different language
23 - Controlling search parameters
24 - Ranking search results
25 - Creating and using dictionaries

4. Creating and Managing Triggers

26 - Introducing triggers
27 - Trigger execution order
28 - Creating a row-level, after insert trigger
29 - Creating a row-level, after update trigger
30 - Creating a statement-level trigger

5. Defining and Launching Transactions

31 - Transactions and transaction isolation levels
32 - Creating and committing transactions
33 - Rolling back transactions
34 - Rolling back committed transactions
35 - Savepoints and rollbacks
36 - The read committed isolation level
37 - The repeatable read isolation level
38 - The serializable isolation level

Conclusion

39 - Summary and next steps