Advanced PostgreSQL

Advanced PostgreSQL

English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 2h 20m | 307 MB

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.

Table of Contents

1 Advanced features in PostgreSQL

Installing and Setting up PostgreSQL
2 macOS Install PostgreSQL and PGAdmin 4
3 macOS Install and set up PostGIS
4 Windows Install PostgreSQL and PGAdmin 4
5 Windows Install and set up PostGIS

Working with Geospatial Data
6 Spatial data analysis using PostGIS
7 The WKT and WKB format
8 Geometry and geography data types
9 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

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

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

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

39 Summary and next steps